System Design · Chapter SD2

Schema design & normalization

Q: How do you model a many-to-many relationship (such as orders and products) in a normalized schema, and what serves as the primary key?

You introduce a third join table (e.g. order_items) that holds a foreign key to each side. Its primary key is the pair of those foreign keys together — PRIMARY KEY (order_id, product_id) — which also prevents the same product being listed twice on the same order.

Design a schema that stays correct and fast: from spotting anomalies, through 1NF→3NF and constraints/indexes, to deliberate denormalization — every step runnable in sqlite3.

⏱️ ~2.5 hours🧪 4 labs🎯 Beginner→Expert

Learning objectives

Explain the anomalies that duplicated data causes.
Normalize a schema 1NF→2NF→3NF and model relationships correctly.
Design keys, constraints, and indexes for correctness and speed.
Know when to deliberately denormalize, and the trade-off it makes.

▶ Runnable companionEvery code block here is also saved under code/sd2-schema-design/ — pure Python / sqlite3, runs with no setup.

1 · Why not one big table? essential

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.

🗺️ How to read this diagram

This picture shows the whole point of the lesson in three steps, read left to right following the arrows. It turns one messy table into two clean, connected tables. A table is just a grid of data (rows and columns), like a spreadsheet.

The left box, "One wide table (repeats)", is the beginner mistake: one big table where the same customer name is copied onto every order row. The small word anomalies underneath means the problems that repetition causes.
The first arrow points to the middle box, "Normalize". "Normalize" is the process of reorganizing the data so each fact is stored only once. 1NF→3NF names the three tidy-up stages you'll do in section 2.
The second arrow points to the right box, "customers + orders (linked)": the finished result — two separate tables that are linked together so you can still see which customer made which order.
The colours just mark the journey: the problem state on the left, the action in the middle, the healthy result (clean + linked) on the right.

In short: Duplicated data is the disease; "normalizing" is the cure. Splitting one repetitive table into linked tables means every fact lives in exactly one place.

Example code for learning — review, test, and adapt it before running against real or production systems. Commands can create, change, or delete resources. See the Terms & Disclaimer.

Step 1 · See an anomaly (runs standalone)

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',)]

▶ How this works

This short program demonstrates the problem that normalization solves. It builds a tiny in-memory database with one badly-designed table, then shows how easily the data becomes inconsistent. sqlite3 is a small database that comes free with Python; ":memory:" means "build it in RAM and throw it away when the program ends" — nothing is saved to disk.

CREATE TABLE bad (...) makes a table with three columns: an order id, the customer's name, and an amount. The flaw is that the name lives inside every order row instead of in its own place.
executemany(...) inserts three rows at once. All three are Ava's orders, so the name "Ava" is written three separate times — the duplication the comment warns about.
The UPDATE ... WHERE order_id=1 line renames Ava to "Ava Lee", but only on order 1. The other two rows are left untouched — exactly the mistake a real person makes when a value is stored in many places.
The final SELECT DISTINCT cust_name asks "what different names are in the table?" — DISTINCT means "list each unique value once". Because the update was incomplete, it finds two names for one real person.

What the output means: It prints [('Ava Lee',), ('Ava',)] — two names for a single customer. That is the update anomaly: when a fact is duplicated, changing it in one spot leaves the copies wrong.

Try this: Add the two missing updates (for order_id=2 and 3) and re-run — now the query returns just one name. Then imagine doing that across a million rows, and you'll feel why we split the table instead.

2 · 1NF → 2NF → 3NF intermediate

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.

Form	Rule (plain)	Fixes
1NF	each cell holds ONE value; no repeating groups	lists crammed in a column
2NF	1NF + non-key columns depend on the WHOLE key	partial-key duplication
3NF	2NF + non-key columns depend on nothing but the key	transitive duplication

Step 2 · A normalized schema (runs standalone)

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']

▶ How this works

This is the fixed design — the same data, but split across four small tables that link to each other so no fact is ever duplicated. executescript(...) runs several CREATE TABLE statements at once. Read each table as "a list of one kind of thing": customers, products, orders, and the lines that connect orders to products.

A PRIMARY KEY is a column whose value uniquely identifies each row — like a customer number. id INTEGER PRIMARY KEY gives every customer, product, and order its own unique id so you can always point to exactly one row.
NOT NULL means "this cell can't be left empty", and UNIQUE (on email) means "no two customers may share this value". These are rules the database itself will enforce.
customer_id INTEGER ... REFERENCES customers(id) is a FOREIGN KEY: instead of copying the customer's name onto the order, the order just stores the customer's id and points to the customers table. This is a one-to-many link — one customer can have many orders. Change the name once in customers and every order sees the new value.
order_items is a join table that solves a many-to-many relationship (one order holds many products; one product appears in many orders). It holds two foreign keys, and PRIMARY KEY (order_id, product_id) uses the pair together as the key, so the same product can't be listed twice on the same order.

What the output means: It prints tables: ['customers', 'products', 'orders', 'order_items'] — the query asks the database to list its own tables, confirming all four were created.

Try this: Trace where Ava's name lives now: only once, in customers. An order never repeats it — it just stores her id. That single change is what makes the update anomaly from Step 1 impossible here.

The join table is the key insightA many-to-many relationship (orders ↔ products) needs a third join table whose primary key is the pair of foreign keys. This one pattern appears in almost every real schema.

3 · Keys, constraints & indexes advanced

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.

Step 3 · Constraints reject bad data; index speeds reads (continues Step 2)

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

▶ How this works

This lab proves two things about a good schema: the constraints (rules) from Step 2 actually block bad data, and an index makes lookups fast. It continues the same database from Step 2, so the four tables already exist.

The try: ... except sqlite3.IntegrityError block deliberately inserts a product with price -5. The schema said CHECK (price >= 0), so the database refuses the write and raises an error, which we catch and print instead of crashing. That's the rule protecting your data automatically.
Next it inserts one customer and then 1000 orders for that customer, using a for loop. This gives us enough rows that speed differences become measurable.
CREATE INDEX idx_orders_cust ON orders(customer_id) builds an index — think of the alphabetical index at the back of a book. Without it the database scans every row to find matches; with it, it jumps straight to the right ones.
EXPLAIN QUERY PLAN asks the database how it would run a query without actually returning the data. We then check whether the plan text contains "USING INDEX" — proof the index is being used.

What the output means: It prints rejected: CHECK constraint failed: price >= 0 (the bad write was blocked) and uses index: True (the fast lookup path was chosen).

Try this: Comment out the CREATE INDEX line and re-run — the last line flips to uses index: False, meaning the database now scans all 1000 rows. That contrast is exactly why indexes matter.

4 · When to denormalize expert

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.

Step 4 · A denormalized total, kept correct with a trigger (runs standalone)

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

▶ How this works

This final lab shows a deliberate exception to the "store each fact once" rule. Re-summing an order's items on every page view can be slow, so we cache (store a ready-made copy of) the order total. The danger is the copy drifting out of date — so we use a trigger to keep it correct by itself.

Two tables are created: orders has a total column that starts at 0, and items holds each line's price and quantity. The total is the duplicated ("denormalized") value we're choosing to store.
A TRIGGER is a rule that runs automatically when something happens. CREATE TRIGGER items_ai AFTER INSERT ON items means "every time a new row is inserted into items, run the following step".
Inside, UPDATE orders SET total = total + NEW.price * NEW.qty WHERE id = NEW.order_id adds the new line's cost (price × quantity) onto that order's running total. NEW refers to the row just inserted. So the total updates itself — no separate code needed.
We insert one order, then two item lines (2 × 10.0 = 20, plus 3 × 5.0 = 15). The trigger fires once per line, so the stored total climbs to 35 without us ever calculating it in Python.

What the output means: It prints cached total: 35.0 — the value was maintained entirely by the trigger, matching what re-summing the items by hand would give.

Try this: Insert a third item line, e.g. (1, 2.0, 4), before the print — the total becomes 43.0 automatically. The trigger is what lets you safely keep a duplicated value without it ever going stale.

Denormalize consciously, never by accidentEvery duplicated value is a chance for data to drift out of sync. Only denormalize a proven hot path, keep it correct with triggers/app logic, and document why. Normalize first; denormalize when the numbers (SD6) demand it.

Exercise SD2.1 — Normalize, constrain, and speed up

Context: A production schema task bundles everything: take a messy wide table and make it correct, safe, and fast — normalized, constrained, indexed, and selectively denormalized where reads demand it.

Your task: Starting from a wide sales(cust_name, cust_email, product, price, qty, date) table, normalize it to 3NF with a join table, add constraints that reject bad data, add the right index and confirm it, and add a denormalized per-order total kept correct with a trigger.

Requirements:

Split sales into customers, products, and an orders/line-items join table in 3NF
Add CHECK, UNIQUE, and FOREIGN KEY constraints and prove each rejects invalid data
Add an index on a filtered/joined column and confirm it via EXPLAIN QUERY PLAN
Add a denormalized per-order total maintained by a trigger
Show the total stays correct as line items are inserted

💡 Hint: Decide which columns describe the customer, which describe the product, and which describe the line item — that partition is your 3NF split before you add constraints and the trigger.

🪜 Practice ladder beginner → industry

Six graded exercises, easy to real-world. Try each before opening its solution.

Exercise 1 · Show an update anomalyBeginner

Context: Before you can appreciate normalization you have to feel the pain it prevents. A single wide table that repeats the same fact on every row is how quietly-corrupt data gets born.

Your task: Build one wide orders_wide table that stores the customer's city on every order row, then demonstrate an update anomaly by changing the city on a single row and showing the table now disagrees with itself.

Requirements:

Create one denormalized table repeating cust and city per order
Insert at least two orders for the same customer with the same city
Update the city on only one of those rows, as a careless UPDATE would
Query distinct (cust, city) pairs and show the customer now has two cities
Articulate that duplicated data is duplicated truth, and truth drifts

💡 Hint: The anomaly appears the moment you update "the row you happened to touch" instead of a single source of truth — a targeted WHERE oid=1 makes it obvious.

Show solution

Duplicated data is duplicated truth — and truth drifts:

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!

Two "cities" for one customer is the anomaly normalization removes by storing each fact once.

Exercise 2 · Normalize to 3NFIntermediate

Context: Third normal form is the default target for transactional schemas: every fact is stored exactly once, so an update touches exactly one place and no anomaly is possible.

Your task: Split the wide table into customers (holding the city) and orders (holding a foreign key to the customer), reinsert the data, and show the city update now touches a single row.

Requirements:

Separate the entities so city lives on customers, not on every order
Give orders a cust_id that references the customer
Reload the same data across the two normalized tables
Update the city in one place and read it back through a JOIN across all orders
Show every order reflects the new city, making the earlier anomaly impossible

💡 Hint: 3NF's rule of thumb — every non-key column depends on the key, the whole key, and nothing but the key — tells you city belongs with the customer, not the order.

Show solution

3NF: every non-key column depends on the key, the whole key, and nothing but the key:

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)]

City is stored once; every order sees the update through the join. No anomaly is possible.

Exercise 3 · Enforce with keys & constraintsAdvanced

Context: Correctness should be the database's job, not something every code path remembers to check. Keys and constraints make invalid states literally unrepresentable.

Your task: Add a FOREIGN KEY, a NOT NULL, and a UNIQUE constraint to the schema, then show the database rejecting both a duplicate email and an order that points at a non-existent customer.

Requirements:

Declare email as NOT NULL UNIQUE on customers
Declare a FOREIGN KEY from orders.cust_id to customers, with PRAGMA foreign_keys = ON
Attempt a duplicate-email insert and catch the resulting IntegrityError
Attempt an orphan-order insert (a customer id that doesn't exist) and catch it too
Show the invariant holds no matter which code path attempts the bad insert

💡 Hint: SQLite only enforces foreign keys when you turn them on; wrap each bad insert in a try/except sqlite3.IntegrityError to prove the rejection.

Show solution

Constraints make invalid states unrepresentable:

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

The database refuses to persist a duplicate email or an order pointing at a non-existent customer — the invariant holds no matter which code path inserts.

Exercise 4 · Index the read path and measureExpert

Context: Indexes are the main read-speed lever, and they are a deliberate trade: faster reads in exchange for slower writes and extra storage. You justify them by measuring, not guessing.

Your task: Insert many rows, then compare the query plan for a filtered lookup before and after adding an index, confirming the engine switches to an indexed search.

Requirements:

Load a substantial number of rows (e.g. tens of thousands) into an events table
Capture the plan for a filtered query and see a full SCAN
Create an index on the filtered column
Capture the plan again and see it become SEARCH ... USING INDEX
State the trade-off: each index also slows inserts, so index only columns you actually filter or join on

💡 Hint: Even offline, the query plan is the proof — you don't need a stopwatch when EXPLAIN QUERY PLAN shows the access path changing.

Show solution

Measure, don't guess — even offline the plan proves the change:

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=?)

The index turns a full scan of 20k rows into a targeted search. Each index also slows inserts, so index the columns you actually filter/join on.

Exercise 5 · When to denormalize (deliberately)Professional

Context: Sometimes you copy data on purpose. When a derived value is read constantly but expensive to compute, caching it on the parent row — kept correct by a trigger — is a legitimate, deliberate denormalization.

Your task: Add a cached order_count column to customers, keep it correct with an AFTER INSERT trigger on orders, and show the counter updating as orders arrive.

Requirements:

Add an order_count column defaulting to zero on customers
Create an AFTER INSERT trigger that increments the owning customer's count
Insert several orders and read the cached count back as a single lookup
Explain the trade: the read is now O(1) instead of a COUNT scan, paid for by write-time bookkeeping
Note this is only worth it when the read is hot and the derived value is expensive

💡 Hint: The trigger references NEW.cust_id to know which customer to bump; the point is that the cache can never silently drift because the database maintains it.

Show solution

Denormalization is a conscious trade: faster reads, more write-time bookkeeping:

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,)

Reads of the count are now O(1) instead of a COUNT scan — paid for by the trigger keeping the cache correct. Only do this when the read is hot and the derived value is expensive.

Exercise 6 · Schema review checklist (validated)Industry scenario

Context: Teams catch the same schema mistakes over and over in review: a table with no primary key, a foreign key to a table that doesn't exist, a hot filter column with no index. Codifying the review makes it a machine check instead of a hope.

Your task: Given a schema described as data, write a review function that asserts every table has a primary key, every foreign key references an existing table, and every hot filter column is indexed — returning the list of issues found.

Requirements:

Represent the schema and the set of hot filter columns as plain data structures
Flag any table missing a primary key
Flag any foreign key whose referenced table is absent from the schema
Flag any hot filter column that is not present in that table's index list
Return a list of human-readable issue strings rather than raising
Demonstrate it catching a real, common bug: a filtered foreign-key column with no index

💡 Hint: Iterate the schema dict once, appending a descriptive string per violation; the value is that the check runs before a migration ships, not after.

Show solution

A machine-checkable review catches the classic mistakes before migration:

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']

The one issue flagged is a real, common performance bug — a foreign-key column that's filtered on but not indexed.

✓ Checkpoint — you can move on when you can…

Explain the three anomalies and how normalization removes them.
Normalize to 3NF and model a many-to-many with a join table.
Enforce rules with constraints and speed reads with indexes.
Decide when to denormalize and keep the copy correct.

Knowledge check check yourself

✓ Knowledge check

Name the three anomalies that duplicating data (e.g. one wide table) causes, and explain what each one is.

Show answer

The update anomaly: changing a value in one place leaves duplicated copies inconsistent (e.g. two names for one customer). The delete anomaly: deleting the last order also loses the customer entirely. The insert anomaly: you can't add a customer who has no orders yet. Normalization stores each fact once so none can occur.

✓ Knowledge check

How do you model a many-to-many relationship (such as orders and products) in a normalized schema, and what serves as the primary key?

Show answer

You introduce a third join table (e.g. order_items) that holds a foreign key to each side. Its primary key is the pair of those foreign keys together — PRIMARY KEY (order_id, product_id) — which also prevents the same product being listed twice on the same order.

Educational content, provided as-is and without warranty. Code samples are examples — review, test, and adapt them before using in production. See the Terms of Use & Disclaimer. Use at your own risk.

Learning objectives

1 · Why not one big table? essential

2 · 1NF → 2NF → 3NF intermediate

3 · Keys, constraints & indexes advanced

4 · When to denormalize expert

Exercise SD2.1 — Normalize, constrain, and speed up

🪜 Practice ladder beginner → industry

✓ Checkpoint — you can move on when you can…

Knowledge check check yourself

More in System design