Schema design & normalization

1 · Why not one big table? essential

Start with a support ticket you'll eventually get. A customer writes in: "Your invoice spells my name 'Ava', but your shipping label says 'Ava Lee', and my account page shows both." Nobody typed it wrong on purpose. What happened is that somewhere in the system her name was stored in a dozen places — once on every order she ever placed — and one day someone updated a few of them and missed the rest. Now the database quietly disagrees with itself, and no amount of careful code will fix it, because the shape of the data invited the mistake. That is the problem this entire chapter exists to prevent.

Here's the tempting move that causes it. When you're new, one big flat table feels obviously simplest: one place to look, one row per order, everything you need on the same line. It's the spreadsheet instinct — and for a spreadsheet you fill in by hand, it's fine. The trouble starts when the same fact (Ava's name, her city, her email) gets copied onto row after row. Think of it like writing your home address on every single envelope in a drawer of a hundred letters: the day you move house, you don't have one address to change — you have a hundred, and you will miss some.

The database name for "the same truth stored in more than one place, drifting apart" is an anomaly, and it comes in three flavours worth naming so you can spot them in a design review. The update anomaly: you change a value in one row and the duplicated copies elsewhere are now wrong (two names for one person). The delete anomaly: a fact you cared about only survived because it was riding along on some other row — delete Ava's last order and you've also erased that she exists at all. The insert anomaly: you can't record a customer until she has an order, because the customer only exists as a column on the orders table — there's nowhere to put a customer who hasn't bought anything yet.

The common mistake isn't ignorance of these rules — it's assuming "I'll just be careful to update every copy." You won't. Nobody does, forever, across every code path and every 2 a.m. hotfix. The fix isn't discipline, it's design: store each fact exactly once, and let everything that needs it point to that one copy. That process is normalization, and when a fact lives in only one place, it is structurally impossible for two copies to disagree — there are no copies. The rest of this section shows the disease in code; the sections after it administer the cure.

Beginners often put everything in one wide table — the customer's name repeated on every order row. That causes anomalies: update a name in one place and it's inconsistent elsewhere (update anomaly); delete the last order and lose the customer entirely (delete anomaly); can't add a customer who has no orders yet (insert anomaly). Normalization stores each fact once so these can't happen.

anomaly.pyimport sqlite3
db = sqlite3.connect(":memory:")
# BAD: customer name duplicated on every order row
db.execute("CREATE TABLE bad (order_id INT, cust_name TEXT, amount REAL)")
db.executemany("INSERT INTO bad VALUES (?,?,?)",
    [(1,"Ava",40),(2,"Ava",60),(3,"Ava",20)])
db.commit()
# Ava changes her name -> must update EVERY row, or data is inconsistent:
db.execute("UPDATE bad SET cust_name='Ava Lee' WHERE order_id=1")   # forgot the rest!
print(db.execute("SELECT DISTINCT cust_name FROM bad").fetchall())  # two names for one person!

[('Ava Lee',), ('Ava',)]

2 · 1NF → 2NF → 3NF intermediate

"Normal form" sounds like abstract theory, so let me hand you the one sentence that makes it practical. Generations of students have memorized 1NF, 2NF, 3NF as three intimidating definitions and then forgotten them the day after the exam — because they learned the letters, not the idea. The idea, said in plain English by database pioneer Bill Kent, is a tiny oath every column swears: each non-key column depends on the key, the whole key, and nothing but the key. Learn that one line and you can re-derive all three forms; the rest is just naming which part of the oath a given mess violates.

Think of it as tidying a house in three passes, each stricter than the last. First pass (1NF): one thing per drawer. No cramming a list of five phone numbers into a single cell, no "products" column holding "apple, pear, milk". Every cell holds a single, atomic value — because the moment you stuff a list into one cell, you can't search it, join it, or constrain it without string-surgery. Second pass (2NF): don't file a fact under half a label. This only bites when your key is made of two columns together (like order_id + product_id). If a column actually depends on only part of that key — say the product's name depends only on product_id, not on which order it's in — it's in the wrong table, duplicated across every order that product appears in. Move it out. Third pass (3NF): no facts filed under other facts. A non-key column must depend on the key directly, not by way of another non-key column. The textbook trap: storing a customer's city and their zip_code on the orders table, when city really depends on zip, which depends on the customer — a chain (a "transitive dependency") that means updating one city means hunting down every order.

Here's the analogy that makes the whole ladder click: a key is the label on a filing folder, and every other fact in that folder should be a fact about the thing named on the label — nothing else. A customer's folder holds facts about the customer. An order's folder holds facts about the order. When you catch a fact about a customer sitting in an order's folder, you've found the thing to move, and you didn't need to remember whether it's a "2NF" or "3NF" violation to know it's wrong. The common mistake beginners make is the opposite reflex — over-normalizing into a maze of tiny tables to feel "correct" — but the honest target for almost every transactional schema is simply 3NF: each fact stored once, in the folder it belongs to. The table below names the three passes formally; read it as a checklist for the oath, not as three separate things to memorize.

Normalization proceeds in normal forms. The practical rule: every non-key column should depend on the key, the whole key, and nothing but the key.

schema.pyimport sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE customers (
    id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE NOT NULL);
CREATE TABLE products (
    id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL NOT NULL CHECK (price >= 0));
CREATE TABLE orders (
    id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id),
    created TEXT NOT NULL);
-- many-to-many via a join table (an order has many products; a product is in many orders)
CREATE TABLE order_items (
    order_id INTEGER NOT NULL REFERENCES orders(id),
    product_id INTEGER NOT NULL REFERENCES products(id),
    qty INTEGER NOT NULL CHECK (qty > 0),
    PRIMARY KEY (order_id, product_id));
""")
print("tables:", [r[0] for r in db.execute(
    "SELECT name FROM sqlite_master WHERE type='table'").fetchall()])

tables: ['customers', 'products', 'orders', 'order_items']

3 · Keys, constraints & indexes advanced

Here's a lesson every team eventually learns the expensive way. A validation rule — "email must be unique," "price can't be negative," "an order must belong to a real customer" — starts life in one place: the signup form. Then a second team adds an admin panel that inserts customers too, and they forget the check. Then a nightly import script writes rows directly. Then someone runs a one-off SQL fix at 3 a.m. Each new door into the database is a new chance to let bad data in, and the rule you "enforced in the app" is only enforced in the one app path that happened to remember it. Six months later you've got a customer with a blank email and an order pointing at a customer id that doesn't exist, and no one can say how.

The fix is to stop guarding the doors and instead put the rule where the data actually lives. A constraint is a rule you declare on the table itself, and the database refuses — for everyone, through every door, forever — to store a row that breaks it. Think of it as the difference between telling each guest "please wipe your feet" and installing a floor that physically won't let mud through. NOT NULL ("this can't be empty"), UNIQUE ("no duplicates of this value"), CHECK ("this must satisfy a condition, like price >= 0"), and FOREIGN KEY ("this must point at a row that really exists") together make whole categories of bad data unrepresentable. The invariant holds no matter which script, panel, or panicked hotfix does the writing — that's the entire point.

The second half of this section is the flip side of correctness: speed. When you ask the database "find all orders for customer 42," its default move is to read every single row and check each one — a "full scan" that's fine for a hundred rows and catastrophic for a hundred million. An index is the cure, and the analogy is exact: it's the alphabetical index at the back of a textbook. Without it, finding every mention of a word means reading the whole book; with it, you flip to the index, find the word, and jump straight to the right pages. A database index on customer_id lets the engine leap to the matching rows instead of scanning past the misses.

But — and this is the mistake that separates juniors from seniors — an index is not free, so you don't index everything "to be safe." Every index is a second sorted copy of that column the database must keep up to date, which means every insert and update now has extra bookkeeping to do, and every index eats storage. The discipline is: index the columns you actually filter on or join on (the ones in your WHERE and JOIN clauses), and leave the rest alone. And crucially, you justify an index by measuring, not guessing — which is why the lab below uses EXPLAIN QUERY PLAN to make the database show you whether it's scanning or seeking, before and after. Let's prove both correctness and speed, and reason about the trade-off.

Constraints make the database enforce your rules (so bad data can't get in), and indexes make queries fast. Let's prove both, and reason about the trade-off.

constraints.py# CHECK/UNIQUE/FK constraints reject bad writes at the DB layer:
try:
    db.execute("INSERT INTO products(name, price) VALUES ('bad', -5)")   # violates CHECK
except sqlite3.IntegrityError as e:
    print("rejected:", e)

# Seed + index the FK we filter on:
db.execute("INSERT INTO customers(name,email) VALUES ('Ava','a@x.com')")
for i in range(1000): db.execute("INSERT INTO orders(customer_id,created) VALUES (1,'2026-01-01')")
db.commit()
db.execute("CREATE INDEX idx_orders_cust ON orders(customer_id)")
plan = db.execute("EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id=1").fetchall()
print("uses index:", "USING INDEX" in plan[0][-1])

rejected: CHECK constraint failed: price >= 0
uses index: True

4 · When to denormalize expert

Now for the plot twist that trips up anyone who's just learned normalization. You spend three sections learning "store every fact exactly once," you internalize it as gospel — and then a senior engineer looks at your beautifully normalized schema and says "add a total column to orders." Wait, isn't that duplicating a value you could compute from the line items? Yes. On purpose. This is the moment you learn that normalization was never the goal; a correct, fast system was the goal, and normalization is the tool you reach for first because it's usually right — not a religion.

Here's the concrete pain that justifies breaking the rule. Imagine the checkout page. Every time it loads, to show the order total, the pure-3NF way is to fetch every line item and re-add them: SUM(price × qty) over maybe dozens of rows, on every single page view, for every customer, all day. The number rarely changes, but you recompute it constantly. It's like recounting the cash in the register from scratch every time someone asks the balance, instead of keeping a running total on a sticky note. Denormalization is that sticky note: you store the computed order_total right on the order row, and reads become a single instant lookup instead of a scan-and-sum. You've traded a little duplication for a lot of read speed — deliberately, on a path you know is hot.

So why not do it everywhere? Because every duplicated value reintroduces exactly the demon section 1 exorcised: the chance that the copy and the truth drift apart. The sticky note is a lie the instant someone adds an item and forgets to update it — and now your cached total silently disagrees with the actual line items, which is arguably worse than being slow, because it's wrong and you trust it. The professional answer is to never rely on remembering: you let the database keep the copy correct automatically, with a trigger (a rule that fires on every insert or update to the source rows) or tightly-controlled application logic, so the cache can't go stale.

The mistake to avoid isn't denormalizing — it's denormalizing by accident, or prematurely, or without a guard. The honest workflow is: normalize to 3NF first (correct by default), then denormalize only a specific path you've measured to be a bottleneck, keep the duplicate correct with a trigger, and leave a comment saying why the exception exists so the next engineer doesn't "clean it up." Denormalize consciously, or not at all. The lab below builds exactly that — a cached total that a trigger keeps honest.

3NF is the default, but deliberate denormalization — storing a computed or duplicated value — trades write complexity for read speed on hot paths. The classic example: a cached order_total so the checkout page doesn't re-sum items on every view.

denormalize.pyimport sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE orders (id INTEGER PRIMARY KEY, total REAL DEFAULT 0);
CREATE TABLE items (order_id INT, price REAL, qty INT);
-- keep the denormalized total correct automatically on every insert:
CREATE TRIGGER items_ai AFTER INSERT ON items BEGIN
    UPDATE orders SET total = total + NEW.price * NEW.qty WHERE id = NEW.order_id;
END;
""")
db.execute("INSERT INTO orders(id) VALUES (1)")
db.executemany("INSERT INTO items VALUES (?,?,?)", [(1,10.0,2),(1,5.0,3)])
db.commit()
print("cached total:", db.execute("SELECT total FROM orders WHERE id=1").fetchone()[0])  # 35.0

cached total: 35.0

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE orders_wide (oid INTEGER, cust TEXT, city TEXT, amt REAL)")
db.executemany("INSERT INTO orders_wide VALUES (?, ?, ?, ?)",
               [(1, "Ada", "London", 250), (2, "Ada", "London", 500)])

# Ada moves; we update only the row we happened to touch
db.execute("UPDATE orders_wide SET city='Berlin' WHERE oid=1")
print(db.execute("SELECT DISTINCT cust, city FROM orders_wide WHERE cust='Ada'").fetchall())
# [('Ada', 'Berlin'), ('Ada', 'London')]  <-- inconsistent!

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, city TEXT)")
db.execute("CREATE TABLE orders (oid INTEGER PRIMARY KEY, cust_id INTEGER, amt REAL)")
db.execute("INSERT INTO customers (name, city) VALUES ('Ada', 'London')")
db.executemany("INSERT INTO orders (cust_id, amt) VALUES (?, ?)", [(1, 250), (1, 500)])

db.execute("UPDATE customers SET city='Berlin' WHERE id=1")   # one place
rows = db.execute(
    "SELECT c.name, c.city, o.amt FROM orders o JOIN customers c ON c.id=o.cust_id"
).fetchall()
print(rows)   # [('Ada', 'Berlin', 250.0), ('Ada', 'Berlin', 500.0)]

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("PRAGMA foreign_keys = ON")          # sqlite needs this on
db.execute("CREATE TABLE customers (id INTEGER PRIMARY KEY, "
           "email TEXT NOT NULL UNIQUE)")
db.execute("CREATE TABLE orders (oid INTEGER PRIMARY KEY, cust_id INTEGER NOT NULL, "
           "FOREIGN KEY(cust_id) REFERENCES customers(id))")
db.execute("INSERT INTO customers (email) VALUES ('ada@x.com')")

for bad, label in [("INSERT INTO customers (email) VALUES ('ada@x.com')", "dup email"),
                   ("INSERT INTO orders (cust_id) VALUES (999)", "orphan order")]:
    try:
        db.execute(bad)
    except sqlite3.IntegrityError as e:
        print(label, "->", type(e).__name__)
# dup email -> IntegrityError
# orphan order -> IntegrityError

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE events (id INTEGER PRIMARY KEY, user_id INTEGER, kind TEXT)")
db.executemany("INSERT INTO events (user_id, kind) VALUES (?, ?)",
               [(i % 1000, "click") for i in range(20000)])

before = db.execute(
    "EXPLAIN QUERY PLAN SELECT * FROM events WHERE user_id = 42").fetchall()
db.execute("CREATE INDEX idx_user ON events(user_id)")
after = db.execute(
    "EXPLAIN QUERY PLAN SELECT * FROM events WHERE user_id = 42").fetchall()
print("before:", before[0][-1])   # SCAN events
print("after :", after[0][-1])    # SEARCH events USING INDEX idx_user (user_id=?)

import sqlite3
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE customers (id INTEGER PRIMARY KEY, order_count INTEGER DEFAULT 0)")
db.execute("CREATE TABLE orders (oid INTEGER PRIMARY KEY, cust_id INTEGER)")
db.execute("INSERT INTO customers (id) VALUES (1)")
db.execute(
    "CREATE TRIGGER bump_count AFTER INSERT ON orders "
    "BEGIN "
    "  UPDATE customers SET order_count = order_count + 1 WHERE id = NEW.cust_id; "
    "END")
db.executemany("INSERT INTO orders (cust_id) VALUES (?)", [(1,), (1,), (1,)])
print(db.execute("SELECT order_count FROM customers WHERE id=1").fetchone())  # (3,)

SCHEMA = {
    "customers": {"pk": "id", "fks": [], "indexes": ["email"]},
    "orders":    {"pk": "oid", "fks": [("cust_id", "customers")], "indexes": ["cust_id"]},
    "events":    {"pk": "id", "fks": [("user_id", "customers")], "indexes": []},  # gap
}
HOT_FILTER_COLS = {"events": ["user_id"]}

def review(schema):
    issues = []
    for t, meta in schema.items():
        if not meta.get("pk"):
            issues.append(f"{t}: no primary key")
        for col, ref in meta.get("fks", []):
            if ref not in schema:
                issues.append(f"{t}.{col}: FK to missing table {ref}")
        for col in HOT_FILTER_COLS.get(t, []):
            if col not in meta.get("indexes", []):
                issues.append(f"{t}.{col}: hot filter column not indexed")
    return issues

print(review(SCHEMA))   # ['events.user_id: hot filter column not indexed']

Knowledge check check yourself