DESIGN INTERMEDIATE lesson-12

Database Design & ERDs

🕑 30 min 📚 Lesson 12 of 15 ✅ 5 quiz questions

Good database design determines whether your queries are fast or slow, your data is clean or corrupt. Learn how to model real-world entities, define relationships, and enforce data integrity with foreign keys.

Section 1

Normalization Goals

Database normalization serves three core goals:

Eliminate redundancy — don't store the same data in multiple places
Ensure data integrity — updates in one place cascade correctly
Enable efficient querying — well-structured tables join predictably

Section 2

Entity Relationship Diagrams (ERD)

An ERD is a visual blueprint of your database. It shows:

Entities — the tables (e.g., Students, Courses, Teachers)
Attributes — the columns (e.g., name, age, email)
Relationships — how entities connect (one-to-many, many-to-many)

Design your ERD on paper or with a tool like dbdiagram.io before writing any CREATE TABLE statements. It saves hours of refactoring.

Section 3

Relationship Types with Code

The three fundamental relationship types determine where foreign keys go and whether junction tables are needed.

SQL — One-to-Many

-- One-to-Many: one customer has many orders
CREATE TABLE customers (
  id    INT PRIMARY KEY AUTO_INCREMENT,
  name  VARCHAR(100) NOT NULL,
  email VARCHAR(150) UNIQUE
);

CREATE TABLE orders (
  id          INT PRIMARY KEY AUTO_INCREMENT,
  customer_id INT NOT NULL,   -- the "many" side holds the foreign key
  total       DECIMAL(10,2),
  order_date  DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

-- Many-to-Many: students and courses (junction table)
CREATE TABLE student_courses (
  student_id  INT,
  course_id   INT,
  enrolled_on DATE DEFAULT (CURRENT_DATE),
  PRIMARY KEY (student_id, course_id),   -- composite primary key
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (course_id)  REFERENCES courses(id)
);

Section 4

One-to-One Relationships

A one-to-one relationship stores optional or extension data in a separate table, linked by sharing the same primary key value.

SQL — One-to-One

-- One user has one profile (optional extension table)
CREATE TABLE user_profiles (
  user_id    INT PRIMARY KEY,   -- same as users.id
  bio        TEXT,
  avatar_url VARCHAR(300),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

The user_profiles table shares the same primary key as users — this enforces the one-to-one constraint. If you delete a user, their profile is automatically deleted via CASCADE.

Section 5

Design Checklist & Common Mistakes

🔗

One-to-One

Share PK; use for optional extension data

Shared PK

1️⃣➡️♾️

One-to-Many

FK lives on the "many" side of the relationship

FK on Many

🔀

Many-to-Many

Requires a junction (bridge) table with two FKs

Junction Table

🔑

Composite PK

Junction tables use (student_id, course_id) as PK

Multi-Column

⚡

CASCADE DELETE

Child rows deleted automatically when parent removed

Auto Delete

🚫

SET NULL

FK set to NULL when parent row is deleted

Orphan Safe

⚠️

Common Anti-Pattern: Comma-Separated Lists

Never store comma-separated lists in a single column (e.g., tags='sql,mysql,db'). Create a separate tags table with a junction table. Comma lists cannot be indexed, joined, or queried efficiently.

🏻 Knowledge Check — Database Design

1. A many-to-many relationship requires…

Correct! Many-to-many relationships need a junction (bridge) table with foreign keys pointing to both related tables.

2. In a one-to-many relationship, the foreign key goes in…

Correct! The FK lives on the "many" side — e.g., orders.customer_id references customers.id.

3. ON DELETE CASCADE means…

Correct! ON DELETE CASCADE automatically removes child rows when their parent row is deleted — maintaining referential integrity.

4. ERD stands for…

Correct! ERD = Entity Relationship Diagram — a visual map of database tables, columns, and their relationships.

5. A composite primary key is…

Correct! A composite PK uses multiple columns together as the unique identifier — common in junction tables: PRIMARY KEY (student_id, course_id).