S035 · The Relational Model — Tables, Keys, Normalisation

Module M04: Databases & SQL · Session 35 of 130 · Track: DE 📋

What you'll be able to do after this session

• Explain The Relational Model to a friend in one minute, out loud, no notes.
• Recognise it when you see it in real code / systems / papers.
• Complete the hands-on exercise at the end without looking up help.

Prerequisites

If you can't answer these in 30 seconds each, redo the linked session first:

• S008 — Data Structures — list, tuple, dict, set (when to use what)

Pre-read (skim before the session)

• 🎥 Intuition (5–15 min) · What is a Relational Database? — IBM Technology — 8 minutes; nails tables, keys, relationships.
• 🎥 Deep dive (30–60 min) · CMU 15-445 Lecture 1 — Relational Model & Algebra (Andy Pavlo) — the canonical grad-level intro; watch just the first hour.
• 🎥 Hands-on demo (10–20 min) · Database Normalization — Decomplexify — 20 min hands-on walk-through of 1NF → 3NF with a realistic table.
• 📖 Canonical article · Codd (1970) — A Relational Model of Data for Large Shared Data Banks — the paper that started it all; skim sections 1 & 2.
• 📖 Book chapter / tutorial · PostgreSQL Docs — Data Definition (Ch. 5) — tables, constraints, keys explained by the reference implementation.
• 📖 Engineering blog · Airbnb Eng — Data Modelling for Growth — how a scaled team keeps its relational model sane.

(a) Intuition · 5 min

The one-paragraph mental model. Why this concept exists, what problem it solves, and the analogy that makes it stick.

In one sentence: Why tables + relationships beat spreadsheets.

Picture the world's messiest Excel sheet for an online store: every row a purchase, columns like customer_name, customer_email, customer_city, product_name, product_price, product_category, quantity, order_date. If a customer updates their email, you must change it in every row where they appear. Miss one → some orders now belong to a ghost. If a product price changes, historical orders lose their true price. The spreadsheet is fighting you.

The relational model (Ted Codd, IBM, 1970) fixes this by storing each real-world entity in its own table and linking them by IDs. Customers live in a customers table with a unique customer_id. Products in products. Orders reference customer_id and product_id. Change an email? One row. Add a new column to products? One place. This isn't just tidiness — it's the mathematical guarantee that the same fact isn't stored twice, so it can't disagree with itself.

Three ideas do all the work: primary key (a column that uniquely identifies each row — like a passport number), foreign key (a column pointing to another table's primary key — a reference), and normalisation (the discipline of splitting tables until no fact is duplicated). Everything SQL, every ORM, every data warehouse still runs on this 55-year-old idea.

The forever gotcha: if the same fact lives in two places, at some point in your system's life it will be updated in one and not the other, and now you have a bug you can't spot until a customer complains. Model to avoid duplication, not to look tidy.

(b) Visual walkthrough · 15 min

Diagrams, worked examples, step-by-step visuals.

Bad (denormalised) — one giant orders table:

Look at row 4 — Alice updated her email. Now rows 1 & 2 are wrong. This is called an update anomaly.

Good (3NF) — three tables linked by keys:

Now Alice's email lives in exactly one row of customers. All her past orders automatically use the new email when joined.

The normal forms in one line each (memorise the first three, cite the rest):

Keys taxonomy:

• Primary key (PK): the chosen unique identifier for a row. Never null, never changes. In Postgres, usually id BIGSERIAL PRIMARY KEY or a UUID.
• Foreign key (FK): a column referencing another table's PK. Enforced by the DB: you can't insert an order for a non-existent customer.
• Natural key: a real-world identifier (email, passport_no). Tempting but risky — real-world things change.
• Surrogate key: a made-up ID (bigint, uuid). Boring, safe. Modern default.
• Composite key: multiple columns together form uniqueness (e.g., (order_id, line_no)).

Worked normalisation. Start: (course_code, course_title, instructor_id, instructor_name, instructor_dept) with PK course_code.

• 1NF? Yes, no repeating groups.
• 2NF? Yes, single-column PK.
• 3NF? No — instructor_name and instructor_dept depend on instructor_id, not on course_code. Split into courses(course_code PK, course_title, instructor_id FK) and instructors(instructor_id PK, name, dept).

(c) Hands-on · 20 min

Code you type and run. Not pseudo-code — real, runnable, copy-pasteable.

You can run this against SQLite (built into Python — no install). Save as rel_lab.py:

import sqlite3, textwrap
 
conn = sqlite3.connect(":memory:")
conn.execute("PRAGMA foreign_keys = ON")   # SQLite quirk — off by default
cur  = conn.cursor()
 
schema = """
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    email       TEXT NOT NULL UNIQUE
);
 
CREATE TABLE products (
    product_id  INTEGER PRIMARY KEY,
    name        TEXT NOT NULL,
    price_cents INTEGER NOT NULL CHECK (price_cents > 0)
);
 
CREATE TABLE orders (
    order_id    INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
    product_id  INTEGER NOT NULL REFERENCES products(product_id),
    qty         INTEGER NOT NULL CHECK (qty > 0),
    order_date  TEXT NOT NULL
);
"""
cur.executescript(schema)
 
cur.executemany("INSERT INTO customers VALUES (?,?,?)", [
    (1, "Alice", "a@x.com"),
    (2, "Bob",   "b@x.com"),
])
cur.executemany("INSERT INTO products VALUES (?,?,?)", [
    (10, "T-shirt", 50000),
    (20, "Mug",     25000),
    (30, "Cap",     30000),
])
cur.executemany("INSERT INTO orders VALUES (?,?,?,?,?)", [
    (1, 1, 10, 2, "2026-01-15"),
    (2, 1, 20, 1, "2026-01-20"),
    (3, 2, 10, 1, "2026-01-22"),
    (4, 1, 30, 1, "2026-02-05"),
])
 
# 1. Show Alice's orders WITH current email (single source of truth)
print("Alice's orders (single-source email):")
for row in cur.execute("""
    SELECT o.order_id, c.email, p.name, o.qty, o.order_date
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    JOIN products  p ON o.product_id  = p.product_id
    WHERE c.name = 'Alice';
"""):
    print("  ", row)
 
# 2. Update Alice's email — one row change, ALL past orders reflect it
cur.execute("UPDATE customers SET email = 'alice@new.com' WHERE customer_id = 1")
 
# 3. Prove foreign keys work — try to insert a bad order
try:
    cur.execute("INSERT INTO orders VALUES (99, 999, 10, 1, '2026-03-01')")
except sqlite3.IntegrityError as e:
    print("FK violation caught:", e)
 
# 4. Requery — email now updated everywhere
print("\nAlice's orders after email change (one UPDATE, all rows updated):")
for row in cur.execute("""
    SELECT o.order_id, c.email FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    WHERE c.name = 'Alice';
"""):
    print("  ", row)
 
conn.close()

Checklist — observe when you run:

1. Schema creation succeeds — PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, CHECK are all standard SQL constraints.
2. Alice's initial orders show a@x.com; after ONE update to the customers table, all four order queries reflect alice@new.com.
3. Trying to insert an order for customer_id=999 (doesn't exist) is rejected by the DB — the model enforces its own integrity.
4. Try to insert a duplicate email — UNIQUE constraint blocks it.
5. Try qty = -1 or price_cents = 0 — CHECK blocks them.

Try this modification: add a categories table (category_id PK, name) and a products.category_id FK column. Insert 3 categories, tag your products, then write a query that lists total revenue per category. Congratulations — that's your first star-schema fragment.

(d) Production reality · 10 min

How this shows up in real systems, gotchas, war stories, common bugs.

Every OLTP database in the world is relational — Postgres, MySQL, SQL Server, Oracle, SQLite (yes, your phone). Even so-called "NoSQL" stores (DynamoDB, Cassandra) reinvent primary keys, secondary indexes, and normalisation trade-offs under new names. Learning the relational model is the transferable data skill.

War story — the missing foreign key. A payments startup skipped FKs "for performance" during MVP. Two years later they discovered ~4% of payments.user_id pointed to non-existent users (deleted accounts, race conditions on user creation). Reconciliation took a two-week engineering effort. Cost of enforcing the FK from day one: ~5 microseconds per insert.

War story — the natural-key mistake. A hospital used patient_email as the primary key. Twins with a shared parent email → collision. Then when someone changed their email, all their medical history disconnected. The retrofit to patient_id BIGSERIAL touched 40 tables and took six months.

Normalisation vs denormalisation — the real production trade-off:

• OLTP (Postgres, MySQL, your app database) → normalise. Reads join a few tables; writes are cheap because each fact is stored once.
• OLAP (Snowflake, BigQuery, Redshift, Databricks, your warehouse) → deliberately denormalise into star/snowflake schemas or "One Big Table." Reads scan billions of rows; joins cost too much. Storage is cheap; query time is expensive.

Gotchas top teams handle:

• Nullable FKs. orders.discount_code_id might be optional. Decide: null vs a special "none" row. Both are legal; be consistent.
• Soft deletes. Adding deleted_at TIMESTAMP breaks unique constraints on "email" (deleted users reserving addresses). Use partial indexes: CREATE UNIQUE INDEX ON users(email) WHERE deleted_at IS NULL.
• UUID vs bigint PKs. UUIDs are safe across services, but random UUIDs kill B-tree insert performance because every insert lands in a different leaf. Use UUIDv7 (time-ordered) or ULIDs for modern systems.
• Enum columns vs lookup tables. Storing status as TEXT CHECK (status IN ('open','closed','pending')) is fast but hard to translate. A statuses(id, code, label) table gives you i18n and metadata at the cost of one join. Both are valid — pick per column.

The senior-engineer tell: they draw the ER diagram on a whiteboard before writing any migration, and every FK is enforced by the database, not "in application code."

(e) Quiz + exercise · 10 min

1. Give a one-sentence definition of primary key and foreign key.
2. Explain "update anomaly" using an example from your own life or the T-shirt table above.
3. What does 3NF forbid, and how do you fix a 2NF table to reach 3NF?
4. Name one situation where you would deliberately denormalise in a production system.
5. Why prefer a surrogate key (bigint id) over a natural key (email) as the primary key?
6. Stretch (connects to S008 Python data structures): A Python dict is a key-value store; a list-of-dicts is a table. Rewrite the customers and orders tables as Python dicts of dicts. What operation becomes easy in Python that SQL does effortlessly, and vice versa?

Explain-out-loud test

If you can't teach these 3 things to a friend without notes, redo the session:

1. What is The Relational Model? (one sentence, no jargon)
2. When would you use it? (one concrete example)
3. When would you NOT use it? (one anti-pattern)

What comes next

• Next session (S036): SQL Basics — SELECT, WHERE, ORDER BY, LIMIT
• Previous (S034): Greedy & Backtracking — When to Use Each
• Hub: The 6-Month Learning Plan

Part of a 130-session evergreen learning series. Session structure: (a) intuition · (b) visual walkthrough · (c) hands-on · (d) production reality · (e) quiz + exercise. Duration: 60 minutes.