Why it matters
When a customer's address is copied onto every order, an address change has to find every copy, and a partial update leaves the table disagreeing with itself. Normalisation is the discipline of removing that duplication by separating entities and referencing them by key. The anomalies it prevents are concrete: an insert that cannot be recorded without inventing a related row, an update that must touch many rows, and a delete that destroys unrelated facts.
Mental model
Normalisation is deduplicating a filing cabinet so each fact lives in exactly one folder, with cross-references elsewhere. The metaphor breaks because reading then requires walking between folders, and at scale that walk is the dominant cost, which is why warehouses re-duplicate on purpose.
How it works
Step 1 of 5
One row, many facts
The wide table copies the address onto every order for that customer and the price onto every line. An address change has to find every copy, and a partial update leaves the table disagreeing with itself.
Active layers
- Wide order table
Hover a layer to read what it holds.
Next: One value per cell
- Removes
- repeating · partial · transitive
- Keeps
- foreign keys, by design
- Denormalise
- with a refresh owner
Figure. One wide order table decomposed to third normal form. Each indented band is a relation a normal form produces and each shaded band names the form; the arrows name the dependency being removed, not a build order. Customer and product are peers removed by one rule. Candidate keys, higher forms and indexing are omitted.
What to notice. The repeated customer id and product id in the view are references, not duplicated facts: an address change still touches one row. The address is lifted from the order row and the price from the line item, and each lands in the table its own column identifies. What the decomposition buys is a single place to change a value; what it costs is a join on every read, which is why warehouses denormalise deliberately and give each copied column an owner and a refresh path.
Text version
A vertical stack. A wide order table holding address and price per row feeds first normal form, which produces an order row with one value per column. Second normal form removes the partial dependency and splits out a line item keyed by order and product. Third normal form takes the address from the order row and the price from the line item and puts each in the table its own column identifies: customer keyed by customer id, and product keyed by product id. A right-hand lane and the left rail carry order id and date, customer id and product id into an order view joined on foreign keys. Five steps cover the wide row, atomic cells, whole-key dependency, key-only dependency, and the join.
- List the entities and the facts that depend on them, then give each entity a key that determines all of its attributes.
- Remove repeating groups so each column holds one value, which is first normal form.
- Remove partial dependencies so every non-key column depends on the whole key, which is second normal form.
- Remove transitive dependencies so non-key columns depend only on the key, which is third normal form.
- Check the higher normal forms only where update anomalies remain, such as overlapping candidate keys.
- Denormalise later and deliberately, with a documented refresh path for each duplicated column.
Trade-offs
| Choice | Good for | Cost | Fails when |
|---|---|---|---|
| Third normal form | Integrity and one place to update | Many joins on read | Read latency dominates and joins are expensive |
| Denormalised reporting table | Fast queries and simple SQL | Refresh logic and stale copies | The copy is refreshed ad hoc and drifts |
| Materialised view | Precomputed joins with a refresh rule | Refresh cost and staleness window | Writes are frequent and freshness matters |
| Entity attribute value | Schema flexibility for sparse attributes | No type safety and painful queries | Attributes are known and queried often |
Good for
Integrity and one place to update
Cost
Many joins on read
Fails when
Read latency dominates and joins are expensive
- Read latency dominates and joins are expensive
- The copy is refreshed ad hoc and drifts
- Writes are frequent and freshness matters
- Attributes are known and queried often
Figure. Each choice with what it buys, what it costs, and the case where it loses.
What to notice. The fourth column is the decision: if the losing case describes your situation, the choice is wrong.
Text version
Four trade-offs: Third normal form is good for Integrity and one place to update, costs Many joins on read, and fails when Read latency dominates and joins are expensive. Denormalised reporting table is good for Fast queries and simple SQL, costs Refresh logic and stale copies, and fails when The copy is refreshed ad hoc and drifts. Materialised view is good for Precomputed joins with a refresh rule, costs Refresh cost and staleness window, and fails when Writes are frequent and freshness matters. Entity attribute value is good for Schema flexibility for sparse attributes, costs No type safety and painful queries, and fails when Attributes are known and queried often
Failure modes
- Update anomaly. One copy of an address changes and reports disagree by customer. Debug: group by the entity key and count distinct attribute values greater than one.
- Insert anomaly. A new product cannot be recorded until an order exists for it. Debug: attempt an insert of the entity alone against the current schema.
- Denormalised drift. A cached column is refreshed by one job and not another. Debug: compare the duplicated column against its source of truth with an anti-join.
In production
Enforce keys and foreign keys in the database, and test uniqueness on the natural key. For every denormalised column, name the job that refreshes it and alert when the anti-join against source returns rows.
Common misconceptions
People think: Normalisation is always the right default.
Actually: It is right for write integrity. Read-heavy analytics often needs controlled duplication with a refresh contract.
People think: Third normal form means no duplicate values anywhere.
Actually: It removes dependencies that cause anomalies, not every repeated value; a foreign key repeats by design.
Interview angle
Walk through the anomaly each form removes, using a concrete table, then say when you would denormalise and how you would keep the copy correct. Weak answers recite definitions without naming the insert, update or delete problem they prevent.
Sources
- Codd, A Relational Model of Data for Large Shared Data Banks — the original relational model and redundancy argument.
- PostgreSQL documentation, Constraints — keys and constraints that enforce normalisation.
- Kimball Group, data warehouse resources — the deliberate denormalisation used in dimensional models.