Week 5: Schema Design & Normalization

1. Why Normalize: Update, Insert & Delete Anomalies

orders_bad
+----+---------------+------------------------+----------+-------+
| id | customer_name | customer_email         | product  | price |
+----+---------------+------------------------+----------+-------+
| 1  | Asha          | asha@example.com       | Mouse    | 12.50 |
| 2  | Asha          | asha@example.com       | Keyboard | 39.99 |
+----+---------------+------------------------+----------+-------+

Three concrete problems this creates:

• Update anomaly — if Asha changes her email, both rows need updating; miss one and the database now contains two different "true" emails for the same person.
• Insert anomaly — Asha can't exist in the system until she places an order, because there's no row to put her in without a product and price.
• Delete anomaly — deleting Asha's only order deletes all record that she exists as a customer at all.

Every one of these traces back to the same root cause: customer facts and order facts are mixed into one table instead of separated — exactly what normalization gives a systematic process for fixing.

2. 1NF, 2NF & 3NF

Each normal form fixes one specific kind of redundancy, building on the one before it.

• 1NF (First Normal Form) — every column holds a single, atomic value; no repeating groups (a column holding a comma-separated list of products is a 1NF violation).
• 2NF — every non-key column depends on the entire primary key, not just part of it (only relevant for tables with a composite primary key).
• 3NF — no non-key column depends on another non-key column (a customer_email column that depends on customer_name rather than directly on the order's own key is a 3NF violation — exactly the pattern in the bad table above).

CREATE TABLE customers (
    id    SERIAL PRIMARY KEY,
    name  TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(id),
    product     TEXT NOT NULL,
    price       NUMERIC(10, 2) NOT NULL
);

Asha's email now lives in exactly one place — updating it is one write, she can exist with zero orders, and deleting an order never touches her customer record at all. All three anomalies from the previous section are gone by construction.

3. Foreign Keys & Referential Integrity

A foreign key doesn't just document a relationship — Postgres actively enforces it, refusing operations that would leave a reference pointing at nothing.

INSERT INTO orders (customer_id, product, price) VALUES (999, 'Mouse', 12.50);
-- ERROR: insert or update on table "orders" violates foreign key constraint
-- customer_id 999 doesn't exist in customers

CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    -- ...
);
-- deleting a customer now automatically deletes their orders too,
-- rather than leaving orphaned rows or failing with an error

ON DELETE CASCADE is a real design decision, not a default to reach for automatically — ON DELETE RESTRICT (the implicit default, blocking the delete entirely while references exist) is often the safer choice for data you'd rather not lose silently as a side effect of an unrelated delete.

4. One-to-Many & Many-to-Many

One-to-many (one customer, many orders) is what every example this week has already modeled — a foreign key on the "many" side. Many-to-many (many students enrolled in many courses) needs a third table in between.

CREATE TABLE students (id SERIAL PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE courses (id SERIAL PRIMARY KEY, title TEXT NOT NULL);

CREATE TABLE enrollments (
    student_id INT NOT NULL REFERENCES students(id),
    course_id  INT NOT NULL REFERENCES courses(id),
    PRIMARY KEY (student_id, course_id) -- a composite key: prevents duplicate enrollment
);

This join table (or "junction table") is the standard pattern — neither students nor courses references the other directly; enrollments sits between them, and each row represents one real enrollment. Its composite primary key (both columns together) is what structurally prevents the same student from enrolling in the same course twice.

5. Hands-on Exercise

6. Knowledge Check

Four quick questions. Expand each to check your answer.