Normalization is a set of rules for deciding which columns belong in which table. The goal is narrow: store each fact exactly once. Everything else — no update anomalies, no contradictory rows — follows from that.
This lesson explains the three forms that matter using the pizza schema, then looks at the two places that schema deliberately breaks the rules and why each is right.
What goes wrong without it
Imagine orders stored in one flat table:
order_id | customer | email | item_1 | item_1_price | item_2 | item_2_price
---------+---------------+---------------------+-----------------+--------------+--------+-------------
1 | Demo Customer | customer@pizza.test | Pepperoni Pizza | 16.99 | Pepsi | 2.99
3 | Demo Customer | customer@pizza.test | Meat Lovers | 20.99 | NULL | NULLThree problems, and they have names:
- Update anomaly. The customer's email is stored on every order. Change it and you must find every row — miss one and the database now holds two different emails for one person, with nothing to say which is right.
- Insert anomaly. You cannot record a customer who has not ordered yet; there is no row to put them in.
- Delete anomaly. Delete their last order and the customer disappears.
And a fourth, practical one: item_1, item_2… caps the order at however
many columns you added, and "which orders contained a Pepsi" becomes a query across every one of
them.
First normal form: one value per cell
No repeating groups, no comma-separated lists. item_1/item_2 is the
violation, and the fix is a second table with one row per item:
customer_order (id, customer_name, ...)
order_item (id, order_id, product_name, quantity, unit_price)Now an order can have any number of items, and each is a row you can filter, join and aggregate.
Almost every real "we cannot query this" problem is a 1NF violation — a column holding
"pepperoni,mushrooms,olives" is the classic.
Second normal form: depend on the whole key
2NF only bites when the primary key is made of several columns. If a table is keyed on
(order_id, product_id) and carries a product_name column,
that name depends on product_id alone — half the key — so it is duplicated across every
order containing that product.
The fix is to move it to the table where its key is the whole key: product. With a
single-column key, 2NF is automatic.
Third normal form: no column depending on another non-key column
This is the one you actually apply. Suppose customer_order carried:
customer_order (id, ..., user_id, user_email, user_full_name)user_email does not depend on the order — it depends on user_id, which
is not the key. So it is stored once per order rather than once per user, and every update anomaly
above comes back. 3NF says move it to app_user and keep only the foreign key.
The informal version is worth memorising: every non-key column depends on the key, the whole key, and nothing but the key.
Where price lives, and why
The pizza menu is a good worked example. Price does not live on product, because a
product does not have a price — it has one per size. Putting
small_price, medium_price, large_price on
product is the 1NF mistake again, wearing a disguise: adding a fourth size means an
ALTER TABLE.
product (id, name, description, type, active)
product_size (id, product_id, size, price) -- UNIQUE (product_id, size)The unique constraint on (product_id, size) is what makes it a real
model rather than a bag of rows: it says a product has at most one price per size. A new size is a
row, not a migration.
Denormalizing on purpose, twice
The pizza schema breaks 3NF deliberately in one place and refuses to in another, and the contrast is the whole point.
order_item snapshots the price
order_item stores product_name, crust_name and
unit_price even though all three could be joined from the catalogue. That looks like
exactly the redundancy 3NF forbids. Watch what it buys:
SELECT i.product_name, i.unit_price AS paid, ps.price AS menu_price_now
FROM order_item i
JOIN product p ON p.id = i.product_id
JOIN product_size ps ON ps.product_id = p.id AND ps.size = i.size
WHERE i.order_id = 1
ORDER BY i.id;+-----------------+-------+----------------+
| product_name | paid | menu_price_now |
+-----------------+-------+----------------+
| Pepperoni Pizza | 16.99 | 16.99 |
| Pepsi | 2.99 | 2.99 |
+-----------------+-------+----------------+Now the menu price goes up by three dollars:
UPDATE product_size SET price = price + 3.00 WHERE product_id = 1;
SELECT i.product_name, i.unit_price AS paid, ps.price AS menu_price_now
FROM order_item i
JOIN product p ON p.id = i.product_id
JOIN product_size ps ON ps.product_id = p.id AND ps.size = i.size
WHERE i.order_id = 1
ORDER BY i.id;+-----------------+-------+----------------+
| product_name | paid | menu_price_now |
+-----------------+-------+----------------+
| Pepperoni Pizza | 16.99 | 19.99 |
| Pepsi | 2.99 | 2.99 |
+-----------------+-------+----------------+The customer still paid 16.99. Without the snapshot, editing the menu would silently rewrite history — last month's revenue report would change, and a receipt would disagree with the card statement.
This is not really a normalization violation at all, once you name the fact correctly.
unit_price is not "the product's price"; it is the price this customer was
charged, which is a fact about the order and is stored exactly once. The same reasoning
explains why product_id is nullable with ON DELETE SET NULL: delete the
product and the order still knows what was bought.
cart stores no prices at all
cart_item records only which product, size and crust were chosen. Every
figure is recomputed from the catalogue when the cart is read.
Opposite decision, same principle. A cart is an intention, not a record — a cart left overnight should pick up today's prices rather than quietly honouring yesterday's, and there should be exactly one source of pricing truth. An order is a historical record and must not move.
The question to ask is not "is this normalized" but "which fact am I storing, and does it change when the source changes?" If it should move, join to it. If it should not, snapshot it.
When to stop
3NF is the working target. Higher forms exist and are rarely the reason a schema is in trouble. Denormalize for performance only after measuring — a join to a well-indexed table is cheap, and the usual reason to duplicate data is a counter or total that would otherwise need an expensive aggregate.
If you do, write down what keeps the copy in step: a trigger, a scheduled job, application code. An undocumented duplicate is a bug waiting for the day the two copies disagree.
What to remember
- Store each fact once. The anomalies follow from not doing it.
- 1NF: one value per cell — a comma-separated list is a table you have not made yet.
- 3NF: every non-key column depends on the key, the whole key, and nothing but the key.
- Snapshot a value when it is a fact about this record; join when it should follow the source.