Normalisation: designing tables that don't contradict themselves
Normalisation is designing tables so that each fact is stored in exactly one place. The usual target is third normal form (3NF): one value per cell, and every column depends on the key, the whole key, and nothing but the key.
Why it matters
If every order row stored the customer's email, changing Priya's email means updating five rows — miss one and the database says two different things. That is an update anomaly. Normalisation designs it out, and it is a favourite interview question.
The idea
First normal form (1NF): one value per cell, no lists like 'Pen, Mug', and no repeating columns like item1, item2.
Second (2NF): in a table with a composite key, every column must depend on the whole key. The product name depends only on product_id, so it belongs in products, not in order_items.
Third (3NF): no column depends on another non-key column — a customer's city name shouldn't carry the city's country too; that belongs in a cities table.
The practice database is already normalised: customers, orders, order_items and products each hold one kind of fact. Denormalisation is copying data back on purpose, for read speed, accepting the risk.
A school keeping each pupil's address in one office file, instead of writing it on every exercise book. When a family moves, one change updates everything — and no book can show an old address.
In a school, looking up the office file is quick. In a database, every split costs a join at read time. That is why analytics warehouses often denormalise on purpose: reads there matter more than updates.
How it works
- Start with one wide "flat" table: order, customer name, email, product, price, quantity.
- Priya's email appears on every line she bought — 9 times. Changing it means 9 updates.
- 1NF: one product per row, not 'Headphones, Backpack' in one cell.
- 2NF: product_name and price depend only on product_id, so they move to products; customer details move to customers.
- 3NF: nothing depends on a non-key column. What is left is the practice database: each fact once, joined back when needed.
Key points
- 1NF: one value per cell; no lists, no repeating column groups.
- 2NF: 1NF, and no column depends on just part of a composite key.
- 3NF: 2NF, and no column depends on another non-key column.
- Normalised data avoids update, insert and delete anomalies; the cost is more joins.
- Denormalise on purpose (reporting tables, caches) when reads matter more than keeping one copy.
Common mistakes
- Storing comma-separated lists in a column.
- Copying a value (like a price) into another table without deciding whether it should change when the source changes.
- Normalising so far that simple questions need eight joins. 3NF is usually the right stop.
Not true: The further a design is normalised, the better it always is.
In fact: Past third normal form, simple questions can need many joins. 3NF is usually the right place to stop.
In the interview
Normalisation organises tables so each fact is stored once, which prevents update, insert and delete anomalies. First normal form means atomic values with no repeating groups. Second: no partial dependency on part of a composite key. Third: no transitive dependency through a non-key column. In short, every column depends on the key, the whole key and nothing but the key. I normalise transactional systems to about 3NF and denormalise deliberately for analytics or read-heavy paths.
Part of Unit 6: Building tables and changing data in the free SQL course.