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 normalized 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.
The previous lesson in this module covered "How Do Foreign Keys Model Relationships?", and the next lesson in this module is "How Do You Design a Schema From a Real Requirement?".
How does the normalization remove the duplication?
You start with the table a report actually wants. There is one row for each order line, and it carries everything the 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, which is 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
13And here is the problem, measured. You group by the pair of customer and city.
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 1The name and the city of Iris Oyelaran sit on seven different rows. That is not a formatting problem. It is seven chances for those seven rows to disagree.
You change her city in one row, and the table now contains two different addresses for the same customer, and no query can tell you which one of them is right.
What are the three normal forms?
The first normal form says that every value in a cell is atomic. There are no lists, there are no repeating groups, and there is 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.
The second normal form says that a table in the first normal form with a composite key has every non-key column dependent on the whole key.
Suppose the key were the pair of order_id and product_id. The customer name depends only on the order_id, and the product name only on the product_id. Both are partially dependent, and the table violates the second normal form.
The fix is to split the table, so the order attributes live in one table and the product attributes in another, with the junction table holding only the relationship.
The third normal form says that 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 the customer, which depends on the key. That is a transitive dependency, and it is a violation of the third normal form.
The fix is the same again. You move signup_date into the customers table, where it depends on the customer key and on nothing else.
Notice that each form is about dependencies, not about looks. The question is always the same one. Does this value depend on the whole key, on a part of it, or on some other value in the row?
What does the normalized shape look like?
There are four tables, and each one holds a single kind of fact. The customers hold customer facts, the products hold product facts, the 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.8It is the same information, there is no duplication, and the query is the only place where the tables meet. Nothing is stored twice, so nothing can disagree.
When is denormalising the right call?
Normalization is not a religion, and its cost is real. There is a three-table join to answer a question that a flat table answers directly.
There are three situations that justify a deliberate exception.
There is a reporting warehouse, where the read volume dwarfs the write volume, and where a star schema deliberately repeats the dimension attributes.
There is a cache table, which holds a denormalised copy that is rebuilt on a schedule and that can be discarded at any moment.
And there is a computed snapshot, where a value is deliberately frozen at the moment that 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 the two places. unit_price cannot, because it is only ever written once, by the line. The customer's city can, because 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 let everything else point at it.
- Ask "what does this value depend on?", and the answer tells you which table it belongs in.
- Keep the transaction close to the entity. An order line belongs with an order and not with a customer.
- Historical values are snapshots, not duplication. Freezing a price at the purchase time is correct.
- Do not denormalise to make one query faster until you have measured, and keep the normalized source of truth.
Quick reference
- The first normal form is one atomic value per cell, with no lists and no repeating groups.
- The second normal form says that, with a composite key, every column depends on the whole key.
- The third normal form says that 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, and never accidentally.
Normalisation drill: take the flat order table above and identify every column that violates the third normal form, then say which existing table each one belongs in. Finally, explain in one sentence why unit_price on an order line is not a normalisation violation.