Lesson 2: The Game's Memory | SQL Database Design

Advanced Schema Design Explained:

Polymorphic Inheritance: Base 'characters' table with specialized 'killers' and 'survivors' subtypes
One-to-One (1:1): Each killer/survivor record extends a character record
One-to-Many (1:M): One Character can have many Perks (character_id in perks table)
Many-to-Many (M:M): Many Characters participate in many Matches (through match_participants table)
Discriminator Column: 'character_type' determines whether to look in killers or survivors table

🎯 ERD Challenge: Design the DbD Store System

Create an ERD for the Dead by Daylight in-game store. Consider these entities:

Players (player_id, username, auric_cells_balance)
Items (item_id, name, price, category)
Purchases (purchase_id, date, payment_method)

Question: What relationships exist between these entities? What type of relationship (1:1, 1:M, or M:M)?

Part 2: SQL Database Creation & Basic Operations

Introduction to SQL

SQL (Structured Query Language) is the language we use to communicate with databases. It's like giving commands to a very organized librarian who knows exactly where every piece of information is stored.

Basic SQL Commands:

CREATE TABLE: Build new tables to store data
INSERT: Add new records to tables
SELECT: Retrieve data from tables
UPDATE: Modify existing records
DELETE: Remove records

Working with Our Dead by Daylight Database

Our database already has pre-loaded tables with killer and perk data. Let's explore and work with this data:

💡 Note about INSERT OR REPLACE

You'll see INSERT OR REPLACE in the examples below. This is a SQLite feature that allows you to run the same INSERT statement multiple times without getting "UNIQUE constraint failed" errors. It either inserts a new record or replaces an existing one with the same primary key.

📝 Try this SQL code (you can run it multiple times safely):

🎯 SQL Challenge 1: Working with Perks Data

Now it's your turn! The database already has a perks table. Practice working with this data:

View all perks: SELECT * FROM perks;
Add a new perk: INSERT INTO perks VALUES (6, 'New Perk', 'Description here', 2, 1);
Filter perks: Show only perks belonging to specific killers

Write SQL queries to explore and modify the perks data.

💡 Hint

• Use SELECT * FROM perks to see all perks
• Try filtering: WHERE owner_character_id = 1 for The Trapper's perks
• Add new perks with INSERT INTO perks VALUES (id, name, description, owner_character_id, tier)

Part 3: Advanced SQL Queries & JOINs

Filtering Data with WHERE Clauses

The WHERE clause lets us filter data based on specific conditions. It's like asking the database: "Show me only the killers with a terror radius greater than 30."

Joining Tables with JOIN Operations

JOIN operations let us combine data from multiple tables. This is essential when we want to see related information together.

🎯 SQL Challenge 2: Complex Query Challenge

Write a query that finds all perks belonging to killers with a terror radius of exactly 32 meters. Your query should display:

Killer name
Perk name
Terror radius

Sort the results by killer name alphabetically.

🏆 Lesson 2 Assessment: Mini Database Project

Final Challenge: Complete DbD Bloodweb Database

Design and implement a complete database system for Dead by Daylight's Bloodweb progression system. Your solution must include:

ERD Design: Create tables for Characters, Bloodweb_Nodes, and Items
Table Creation: Use proper SQL to create all tables
Data Population: Insert realistic sample data
Query Demonstration: Write 5 different types of queries

Example Queries to Include:

Find all items available at Bloodweb level 10
Show the most expensive items in each category
Count how many perks each character has
List all add-ons for a specific killer
Find characters with Bloodweb level > 25

Lesson 2: The Game's Memory - SQL Database Design & ERDs

Part 1: Entity-Relationship Diagrams (ERDs)

What are Entity-Relationship Diagrams?

Dead by Daylight ERD Example

📝 SQL Schema