Normalization vs. denormalization
Hook
The application team ships a customer table where the account manager's name is stored on every order row. It works fine — until a manager changes, half the rows get updated, and now the same customer shows two managers depending on which order you read. Someone calls this a bug and "fixes" it by adding a nightly job to reconcile the rows. The real fix was a modelling decision made months earlier, and it has a name: normalization.
Concept
Normalization is organizing tables so that each fact is stored exactly once. You split data across related tables and connect them with keys, so that a supplier name, a customer's country, or a product's price lives in one place and one place only. The payoff is that a change is a single-row update, and the data can never contradict itself.
The rules are staged as normal forms. You do not need to recite them, but you should recognize what each one removes:
| Normal form | Removes | Plain-language rule |
|---|---|---|
| 1NF | Repeating groups and multi-valued cells | One value per cell; each row is atomic |
| 2NF | Partial dependency on part of a composite key | Every non-key column depends on the whole key |
| 3NF | Transitive dependency (non-key depends on non-key) | Non-key columns depend on the key, nothing but the key |
Third normal form (3NF) is the practical target for a source system: no column is derived from another non-key column. It kills three classic failures. An update anomaly is the manager-on-every-row problem — one logical change forces many row edits, and any missed row corrupts the data. An insertion anomaly blocks recording a new product until it has an order to attach to. A deletion anomaly loses a supplier's details the moment their last order is removed. Normalization exists to prevent all three.
Denormalization is the deliberate reverse: you copy related data back into one wider table so a read needs fewer joins. You accept redundancy — and the update anomalies that come with it — in exchange for faster, simpler reads. Crucially, denormalization is a choice you make with eyes open, not the absence of design.
The deciding question is the access pattern, not a rule of thumb:
- A source system (OLTP) does many small writes and single-row reads. It must stay consistent under concurrent edits, so it normalizes — usually to 3NF.
- An analytics system (OLAP) does few, huge, mostly read-only scans that join and aggregate. Every join it avoids at read time is time saved across millions of rows, so it denormalizes on purpose, and it manages the redundancy by loading data in controlled batches rather than editing rows live.
The two are not in conflict; they serve different jobs. The rest of this course is about the disciplined denormalization analytics uses — the dimensional model.
Worked example
Let me make the anomaly concrete, then show the normalized shape that prevents it. Start with one wide table that repeats the supplier on every order line — the shape an inexperienced source design might use.
import sqlite3
con = sqlite3.connect(":memory:")
con.executescript("""
CREATE TABLE order_lines_flat (
order_id INTEGER,
product_name TEXT,
supplier TEXT,
quantity INTEGER
);
INSERT INTO order_lines_flat VALUES
(5001, 'House Blend 500g', 'Highland Farms', 2),
(5002, 'House Blend 500g', 'Highland Farms', 1),
(5003, 'House Blend 500g', 'Highland Farms', 4);
""")
The supplier for that product changes. A partial update — one row missed, exactly the kind of mistake a real job makes — leaves the table contradicting itself:
con.execute("""
UPDATE order_lines_flat
SET supplier = 'Summit Estates'
WHERE product_name = 'House Blend 500g' AND order_id = 5002
""")
conflict = con.execute("""
SELECT COUNT(DISTINCT supplier)
FROM order_lines_flat
WHERE product_name = 'House Blend 500g'
""").fetchone()[0]
print("distinct suppliers for one product:", conflict)
distinct suppliers for one product: 2
One product now has two suppliers. Nothing errored; the data is simply wrong,
and every report grouping by supplier is now wrong too. The 3NF fix stores the
supplier once, in a products table, and references it by key:
con.executescript("""
CREATE TABLE products (
product_id INTEGER PRIMARY KEY,
product_name TEXT,
supplier TEXT
);
CREATE TABLE order_lines (
order_id INTEGER,
product_id INTEGER,
quantity INTEGER
);
INSERT INTO products VALUES (1, 'House Blend 500g', 'Highland Farms');
INSERT INTO order_lines VALUES (5001, 1, 2), (5002, 1, 1), (5003, 1, 4);
""")
con.execute("UPDATE products SET supplier = 'Summit Estates' WHERE product_id = 1")
supplier = con.execute(
"SELECT supplier FROM products WHERE product_id = 1"
).fetchone()[0]
print("supplier now:", supplier)
con.close()
supplier now: Summit Estates
The change is a single-row update, and the supplier can never disagree with
itself because it exists in exactly one row. That is the guarantee a source
system needs. An analytics query, by contrast, would happily read the wide
order_lines_flat shape — as long as a controlled batch load, not live edits,
is what keeps the redundant supplier column correct. Same data, opposite design,
because the access patterns are opposite.
Hands-on
Your turn, on a different dataset. The exercise below hands you a wide,
denormalized subscriptions_flat table that repeats each plan's monthly price
on every subscriber row. You will identify the anomaly, then split it into two
3NF tables — a plans table holding each price once and a subscriptions table
referencing it by key.
Success criteria: after your split, each plan's price appears in exactly one row, and a query that changes a plan's price touches a single row. SQL Kingdom checks both the resulting schema and the single-row update automatically.
Recap
- You can name what normalization protects: update, insertion, and deletion anomalies, by storing each fact exactly once.
- You can explain why source systems target 3NF and why analytics deliberately denormalizes for read speed.
- You can choose between the two by the access pattern — many small consistent writes versus few large read-mostly scans — rather than by dogma.
Next up: the disciplined form of denormalization that analytics actually uses. You will design a dimensional model, where facts and dimensions each have a job and the grain of the fact table is the first decision you make.
- Normalization stores each fact once, preventing update, insertion, and deletion anomalies; 3NF is the practical source-system target.
- Denormalization deliberately copies data into wider tables to cut read-time joins, accepting redundancy that batch loads keep correct.
- Choose by access pattern: OLTP writes many small rows consistently; OLAP reads few huge scans fast.