Normalization is the process of splitting a wide table so that each fact is stored once. It is not about making tables smaller or queries faster — it is about making the data incapable of disagreeing with itself. A normalised schema trades a little query complexity for the removal of an entire class of bug, and that trade has been worth making for fifty years.
How does normalization remove duplication?
Start with the table a report actually wants. One row per order line, carrying everything that line needs to display:
CREATE TABLE flat_orders (
order_id INTEGER,
line_no INTEGER,
customer TEXT, customer_city TEXT, product TEXT, category TEXT,
quantity INTEGER, unit_price REAL, order_total REAL, placed_on TEXT
);
Populated by joining the real tables together, it holds thirteen rows — the number of order lines in the shop:
INSERT INTO flat_orders (order_id, line_no, customer, customer_city, product, category, quantity, unit_price, order_total, placed_on) VALUES
(1, 1, 'Iris Oyelaran', 'Bristol', 'Enamel Mug', 'drinkware', 3, 9.95, 63.3, '2025-01-08'),
(1, 2, 'Iris Oyelaran', 'Bristol', 'Cast Iron Pan', 'cookware', 1, 42.5, 42.5, '2025-01-08'),
(2, 1, 'Peter Kuznetsov', 'Leeds', 'Glass Carafe', 'drinkware', 1, 31.75, 31.75, '2025-01-21'),
(3, 1, 'Iris Oyelaran', 'Bristol', 'Bamboo Tray', 'serving', 2, 24.0, 48.0, '2025-02-14'),
(3, 2, 'Iris Oyelaran', 'Bristol', 'Linen Tea Towel', 'textiles', 1, 14.5, 14.5, '2025-02-14'),
(4, 1, 'Aiko Tanabe', 'Bristol', 'Enamel Mug', 'drinkware', 4, 9.95, 39.8, '2025-03-05'),
(5, 1, 'Peter Kuznetsov', 'Leeds', 'Enamel Mug', 'drinkware', 2, 9.95, 19.9, '2025-04-17'),
(5, 2, 'Peter Kuznetsov', 'Leeds', 'Glass Carafe', 'drinkware', 1, 31.75, 31.75, '2025-04-17'),
(6, 1, 'Chioma Obi', 'Bristol', 'Bamboo Tray', 'serving', 1, 24.0, 24.0, '2025-05-09'),
(7, 1, 'Felix Baumann', 'Leeds', 'Cast Iron Pan', 'cookware', 1, 42.5, 42.5, '2025-06-02'),
(9, 1, 'Iris Oyelaran', 'Bristol', 'Cast Iron Pan', 'cookware', 2, 42.5, 85.0, '2025-07-15'),
(9, 2, 'Iris Oyelaran', 'Bristol', 'Bamboo Tray', 'serving', 1, 24.0, 24.0, '2025-07-15'),
(9, 3, 'Iris Oyelaran', 'Bristol', 'Linen Tea Towel', 'textiles', 3, 14.5, 43.5, '2025-07-15');
SELECT COUNT(*) AS rows_in_flat_table FROM flat_orders;
rows_in_flat_table
13
And here is the problem, measured. Group by the customer and city pair:
SELECT customer, customer_city, COUNT(*) AS rows_repeating_the_pair
FROM flat_orders GROUP BY customer, customer_city
ORDER BY rows_repeating_the_pair DESC
LIMIT 5;
customer customer_city rows_repeating_the_pair
--------------- ------------- ----------------------
Iris Oyelaran Bristol 7
Peter Kuznetsov Leeds 3
Aiko Tanabe Bristol 1
Chioma Obi Bristol 1
Felix Baumann Leeds 1
Iris Oyelaran's name and city sit on seven different rows. That is not a formatting problem — it is seven chances for the seven rows to disagree. Change her city in one row and the table now contains two different addresses for the same customer, and no query can tell which is right.
What are the three normal forms?
First normal form: every value in a cell is atomic — no lists, no repeating groups, no packed "Bristol, Leeds" in one field. The flat table passes this, mostly, but a variant that stored a comma-separated list of product names would fail it.
Second normal form: a table in first normal form with a composite key must have every non-key column dependent on the whole key. Suppose the key were (order_id, product_id). The customer name depends only on order_id, and the product name only on product_id — so both are partially dependent and the table violates second normal form. The fix is to split the table so that order attributes live in one table and product attributes in another, with the junction table holding only the relationship.
Third normal form: no non-key column depends on another non-key column. If the flat table also carried the customer's signup_date, then signup_date would depend on customer, which depends on the key — a transitive dependency, and a violation of third normal form. The fix is the same again: move signup_date into the customers table, where it depends on the customer key and nothing else.
Notice that each form is about dependencies, not about looks. The question is always the same: does this value depend on the whole key, on part of it, or on some other value in the row?
What does the normalised shape look like?
Four tables, each holding one kind of fact: customers hold customer facts, products hold product facts, orders hold order facts, and order_items holds the relationship between an order and a product. The flat report is still one query away:
SELECT o.order_id, c.full_name, c.city, p.name, p.category,
i.quantity, i.unit_price, ROUND(i.quantity * i.unit_price, 2) AS line_total
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN order_items i ON i.order_id = o.order_id
JOIN products p ON p.product_id = i.product_id
ORDER BY o.order_id
LIMIT 6;
order_id full_name city name category quantity unit_price line_total
-------- --------------- ------- --------------- --------- -------- ---------- ----------
1 Acme Ltd Bristol Enamel Mug drinkware 3 9.95 29.85
1 Acme Ltd Bristol Cast Iron Pan cookware 1 42.5 42.5
2 Peter Kuznetsov Leeds Glass Carafe drinkware 1 31.75 31.75
3 Acme Ltd Bristol Bamboo Tray serving 2 24.0 48.0
3 Acme Ltd Bristol Linen Tea Towel textiles 1 14.5 14.5
4 Aiko Tanabe Bristol Enamel Mug drinkware 4 9.95 39.8
The same information, no duplication, and the query is the only place the tables meet. Nothing is stored twice, so nothing can disagree.
When is denormalising the right call?
Normalisation is not a religion, and the cost of it is real: a three-table join to answer a question that a flat table answers directly. Three situations justify a deliberate exception. A reporting warehouse, where read volume dwarfs write volume and a star schema deliberately repeats dimension attributes. A cache table, holding a denormalised copy that is rebuilt on a schedule and can be discarded at any moment. And a computed snapshot, where a value is deliberately frozen at the moment it was true — the unit_price on an order line is denormalised on purpose, because the historical fact matters more than the current one.
The tell for a bad denormalisation is that the duplicated value can change independently in two places. The unit_price cannot: it is only ever written once, by the line. The customer's city can: it exists in the customers table and, in a denormalised copy, in the order too — and those two copies will eventually disagree.
What are the rules of thumb?
- Store each fact in exactly one place, and everything else points at it.
- Ask "what does this value depend on?" — the answer tells you which table it belongs in.
- Keep the transaction close to the entity: an order line belongs with an order, not with a customer.
- Historical values are snapshots, not duplication — freezing a price at purchase time is correct.
- Do not denormalise to make one query faster until you have measured, and keep the normalised source of truth.
Quick reference
- First normal form: one atomic value per cell, no lists and no repeating groups.
- Second normal form: with a composite key, every column depends on the whole key.
- Third normal form: no non-key column depends on another non-key column.
- Each violation is fixed by splitting the table and moving the offending value to the table it depends on.
- Denormalise deliberately for warehouses, caches and historical snapshots — never accidentally.
Normalisation drill: take the flat order table above and identify every column that violates third normal form, then say which existing table each one belongs in. Finally, explain in one sentence why the unit_price on an order line is not a normalisation violation.