Normalization: 1NF to BCNF
Normalization removes update, insert and delete anomalies by making sure every fact is stored once; each normal form forbids one more way a fact can hide inside a table.
Anomalies: the problem normalization solves
Store a customer’s email on every order they place and you have created three bugs waiting to happen. Update anomaly: correcting the email means updating N rows, and a partial update leaves two emails. Insert anomaly: you cannot store a customer who has not ordered, because the only place customers live is the orders table. Delete anomaly: deleting their last order deletes the customer.
All three come from the same mistake: one table holds facts about two different things. Normalization is the discipline of noticing that and splitting the table so that each fact has exactly one home.
The forms, as rules
1NF: every column holds one atomic value; no repeating groups (product_1, product_2, …); every row has a key. The fix for a repeating group is a child table with one row per repetition.
2NF: 1NF, and every non-key column depends on the *whole* primary key. Only violable with a composite key: in order_items(order_id, product_id, product_name), product_name depends on product_id alone — move it to products.
3NF: 2NF, and no non-key column depends on another non-key column. In orders(id, customer_name, customer_email), email depends on the customer, not on the order — move the customer to customers. The slogan: every non-key column depends on the key, the whole key, and nothing but the key.
BCNF: for every dependency X → Y, X is a candidate key. Catches the rare case 3NF misses, where a non-key column determines part of the key. Splitting to BCNF can lose the ability to enforce some constraints in the schema; 3NF plus a CHECK is sometimes the better engineering answer.
Functional dependencies: the underlying idea
X → Y ("X determines Y") means that whenever two rows agree on X they agree on Y. product_id → product_name, order_id → customer_id, customer_id → email. A table is well-formed when every dependency has a key on its left side. Every normal form is a way of saying "a dependency whose left side is not a key is a fact about something else — give it its own table".
You do not need to memorise the forms if you can spot dependencies. Ask of each column: "what does this depend on?" If the answer is not "the primary key of this table", it belongs elsewhere.
How far to go
3NF is the practical target for transactional schemas. It removes every anomaly that matters in practice, and the tables it produces are the ones the planner is good at joining. Beyond 3NF you are trading readability for theoretical purity that rarely pays.
Normalization optimises for write correctness, not read speed. A dashboard that joins six normalised tables on every page view may be correct and still too slow; the answer is not to un-normalise the source of truth but to *add* a denormalised copy for that read — see Denormalization on Purpose.
Key points
- Update, insert and delete anomalies all come from one table holding facts about two things.
- 1NF: atomic, no repeating groups. 2NF: whole key. 3NF: nothing but the key. BCNF: every determinant is a key.
- Spot functional dependencies whose left side is not a key — each one is a hidden entity.
- 3NF is the practical target; denormalise by adding copies, not by un-normalising the source.
Normalise a real bad table
Products are stored as numbered columns. The number of columns decides the maximum number of products per order, and every query has to know that number.
orders
──────────────────────────────────────────────────────────────────────────────────────────
order_id customer_name customer_email product_1 price_1 product_2 price_2
──────── ───────────── ─────────────────── ────────── ─────── ────────── ─────────
1001 Jonas Olsen jonas@example.com Keyboard 89.00 Monitor 349.00
1002 Carla Ivanov carla@example.com Monitor 349.00 NULL NULL
1003 Jonas Olsen jonas@exmaple.com Laptop 1299.00 Keyboard 89.00
↑ typo, and nothing stops it- Structural: An order with three products does not fit. Adding `product_3` means a migration and rewriting every query.
- Query: "How many keyboards did we sell?" has to check product_1 OR product_2 OR … There is no single column to index.
- Update: Jonas's email is stored on every one of his orders. Row 1003 has a typo, so he now has two email addresses and no row is authoritative.
- Insert: You cannot record a product that has never been ordered — there is nowhere to put it.
- Delete: Deleting order 1003 deletes the only record that a Laptop costs 1299.00.
Nothing yet — this is the starting point.
When to use — and when not
- The system of record for anything that gets updated.
- Whenever you catch yourself writing
product_1, product_2.
- Analytics tables and event logs that are written once and never updated — a wide denormalised row is the correct shape there.
Failure modes
- Repeating groups as numbered columns.
- Customer facts on the order.
- Normalising a read-only reporting table into a six-way join for no benefit.