🏗️ Lesson 16: Database Design & Normalization

Why Design Matters

Imagine a spreadsheet where one column is "Customer Name," another is "Customer Email," another is "Customer Phone" — and these same values are repeated on every single order row. Change a customer's email? You'd have to update it in 50 different rows. Miss one, and now you have two conflicting emails for the same person.

That's what happens in an unnormalized database. Normalization fixes these problems by organizing data into well-structured tables where each fact is stored exactly once.

The Problem: A Badly Designed Table

Let's start with a table that stores everything about orders in one flat structure — the kind of thing you might see in a spreadsheet export:

The "Everything" Table

At first glance it looks fine. But look closer — there are serious problems hiding in this design.

Update, Insert, & Delete Anomalies

The "everything" table suffers from three classic problems:

1. Update Anomaly

Alice's email appears in both rows. If she changes her email, you must update every single row containing her data. Miss one? Now you have two different emails for the same customer. Which is correct?

2. Insert Anomaly

You want to add a new customer (Carol) who hasn't placed an order yet. But every row requires an order_id and order_date. You can't add a customer without faking an order — or leaving required fields NULL.

3. Delete Anomaly

If you delete Bob's only order (order 3), you lose all information about Bob — his name, email, and city are gone. Deleting an order shouldn't erase a customer.

These problems exist because different facts are mixed together in one table. Customer data, order data, and book data should live in separate tables. That's what normalization achieves.

First Normal Form (1NF)

The Violation

Look at our "everything" table — the book_titles, book_prices, and quantities columns contain comma-separated lists:

"Dune, 1984" in one cell is not atomic — it's two values crammed together. You can't easily query "find all orders containing 1984" without messy string parsing.

The Fix: One Fact Per Cell

Split each item into its own row:

Now every cell has exactly one value. The primary key would be (order_id, book_title) — a composite key identifying each unique order-item combination.

Functional Dependencies

Before tackling 2NF and 3NF, you need to understand functional dependencies — they're the reasoning tool behind normalization.

A functional dependency means: if you know the value of column A, you can determine the value of column B. We write this as:

A → B    (A determines B)

Examples from Our Table

The composite primary key is (order_id, book_title). Notice that some columns depend on the whole key (quantity), while others depend on only part of it (order_date depends only on order_id). That partial dependency is exactly what 2NF eliminates.

Second Normal Form (2NF)

The Violation

Our 1NF table has primary key (order_id, book_title). But several columns depend on only part of that key:

• order_date, cust_name, cust_email, cust_city depend on order_id alone
• book_price depends on book_title alone

Only quantity depends on the full composite key (order_id + book_title).

The Fix: Split Into Separate Tables

Move each group of columns to its own table, keyed by the column they actually depend on:

orders — columns that depend on order_id

books — columns that depend on book_title

order_items — columns that depend on the full key (order_id, book_title)

Third Normal Form (3NF)

The Violation

In our orders table:

order_id → cust_name → cust_email, cust_city

The customer's email and city don't depend on the order — they depend on the customer. If Alice changes her email, you still have to update every order row that mentions her. That's a transitive dependency: order_id determines cust_name, and cust_name determines cust_email.

The Fix: Extract the Customer

Move customer data into its own table and reference it with a foreign key:

customers

orders — now just stores the order itself and a reference to the customer

This final schema is exactly the bookstore design we've been using throughout this course — customers, orders, order_items, books, authors. Now you know why it's structured that way.

Normalization at a Glance

Entity-Relationship Diagrams

An ER diagram is a visual blueprint of your database. It shows tables (entities), their columns (attributes), and the relationships between them. ER diagrams are the standard tool for planning and communicating database designs.

Reading ER Diagrams

The relationship lines tell you how tables connect:

Our Bookstore ER Diagram

This diagram tells the whole story at a glance: an author writes many books, a customer places many orders, each order contains many items, and each item refers to one book.

Design Walkthrough: From Requirements to Tables

Let's design a database from scratch. Here are the requirements for a music streaming service:

Step 1: Identify Entities

Nouns in the requirements become tables: users, artists, albums, songs, playlists.

Step 2: Identify Relationships

• Artist → Albums: one-to-many (one artist has many albums)
• Album → Songs: one-to-many (one album has many songs)
• User → Playlists: one-to-many (one user creates many playlists)
• Playlist ↔ Songs: many-to-many (a playlist has many songs, a song can be in many playlists) → needs a junction table

Step 3: Draw the ER Diagram

Step 4: Write the SQL

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    signup_date DATE NOT NULL DEFAULT (CURRENT_DATE)
);

CREATE TABLE artists (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    genre VARCHAR(50)
);

CREATE TABLE albums (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    release_year YEAR,
    artist_id INT NOT NULL,
    FOREIGN KEY (artist_id) REFERENCES artists(id)
);

CREATE TABLE songs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    duration_seconds INT NOT NULL,
    album_id INT NOT NULL,
    FOREIGN KEY (album_id) REFERENCES albums(id)
);

CREATE TABLE playlists (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    user_id INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE playlist_songs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    playlist_id INT NOT NULL,
    song_id INT NOT NULL,
    position INT NOT NULL,
    FOREIGN KEY (playlist_id) REFERENCES playlists(id) ON DELETE CASCADE,
    FOREIGN KEY (song_id) REFERENCES songs(id),
    UNIQUE (playlist_id, position)
);
                

Step 5: Verify Normalization

• 1NF — all values are atomic, every table has a primary key ✅
• 2NF — no composite PKs with partial dependencies (we used auto-increment IDs) ✅
• 3NF — no non-key column depends on another non-key column (artist genre depends on artist ID, not on album; user email depends on user ID, not on playlist) ✅

When to Denormalize

Normalization is the default — but sometimes you intentionally denormalize (add controlled redundancy) for performance or simplicity.

Why Denormalize?

• Performance: A dashboard query that JOINs 8 tables on every page load might be too slow. Storing a pre-computed total_spent column on the customer row eliminates the JOINs.
• Read-heavy systems: If you read data 10,000 times for every 1 write, optimizing for reads (even at the cost of write complexity) makes sense.
• Reporting tables: Analytics and warehousing often use "flat" denormalized tables because analysts need fast, simple queries — not normalized schemas.

Common Denormalization Strategies

Exercises

-- vendors (extracted to remove transitive dependency)
CREATE TABLE vendors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    city VARCHAR(100)
);

-- invoices (references vendor)
CREATE TABLE invoices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_date DATE NOT NULL,
    vendor_id INT NOT NULL,
    FOREIGN KEY (vendor_id) REFERENCES vendors(id)
);

-- invoice_items (atomic values, full key dependency)
CREATE TABLE invoice_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT NOT NULL,
    item_name VARCHAR(100) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id)
);
                        

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    join_date DATE NOT NULL DEFAULT (CURRENT_DATE)
);

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE recipes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    prep_time_minutes INT,
    cook_time_minutes INT,
    user_id INT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE recipe_ingredients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    recipe_id INT NOT NULL,
    ingredient_name VARCHAR(100) NOT NULL,
    quantity DECIMAL(8,2),
    unit VARCHAR(30),
    FOREIGN KEY (recipe_id) REFERENCES recipes(id) ON DELETE CASCADE
);

-- Junction table: recipes ↔ categories (M:M)
CREATE TABLE recipe_categories (
    recipe_id INT NOT NULL,
    category_id INT NOT NULL,
    PRIMARY KEY (recipe_id, category_id),
    FOREIGN KEY (recipe_id) REFERENCES recipes(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- Junction table: users ↔ recipes bookmarks (M:M)
CREATE TABLE bookmarks (
    user_id INT NOT NULL,
    recipe_id INT NOT NULL,
    saved_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, recipe_id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (recipe_id) REFERENCES recipes(id) ON DELETE CASCADE
);
                        

Summary

Quick Reference

📚 Additional Resources

• MySQL Docs — Using Databases
• Wikipedia — Database Normalization
• Wikipedia — Entity-Relationship Model

🚀 What's Next?

That completes Module 5: Database Design! You now know how to make queries fast (indexes), simplify complex queries (views), and design clean schemas from scratch (normalization). In Module 6: Advanced Features, you'll learn to build stored procedures and functions — reusable blocks of SQL logic that live inside the database and can be called by name.