Normalisation
Date: 2026-08-17
Organising tables so each fact is stored once. It prevents a category of update bug entirely — and the deliberate reversal of it, denormalisation, is a performance decision that trades that safety back for speed.
Normalisation structures a database so that every fact lives in exactly one place. Denormalisation duplicates facts deliberately, accepting the risk of divergence in exchange for cheaper reads.
The problem, shown
An unnormalised orders table:
order customer email city
1001 J Smith j@example.com Leeds
1002 J Smith j@example.com Leeds
1003 J Smith jsmith@gmail.com Leeds
↑ which is right?
1004 J Smith j@example.com Leed
↑ typo
Three anomalies, and they’re the reason the whole topic exists:
UPDATE ANOMALY change the email → must
update every row, or they
disagree
INSERT ANOMALY cannot record a customer
until they place an order
DELETE ANOMALY delete their last order →
lose the customer entirely
Normalised:
CUSTOMERS
id name email city
7 J Smith j@example.com Leeds
ORDERS
id customer_id total
1001 7 49.98
1002 7 24.99
One email, one place, one update. The anomalies are gone by construction rather than by discipline.
The forms, practically
1NF no repeating groups; one value
per cell
→ not "tags: red,blue" in one column
2NF 1NF + every non-key column depends
on the WHOLE key
→ matters only with composite keys
3NF 2NF + no non-key column depends on
another non-key column
→ city shouldn't be derivable from
postcode within the same table
3NF is where practical work stops. BCNF, 4NF and 5NF exist and are rarely the difference between a working system and a broken one.
The informal version is enough for most decisions: every non-key column should describe the key, the whole key, and nothing but the key.
When to denormalise deliberately
Normalisation optimises writes and integrity; reads pay for it in joins. Denormalise when reads dominate and the join is measurably expensive.
LEGITIMATE
order stores the price paid
← the product's price will change;
the historical fact must not
order stores the delivery address
← the customer will move; the order
shipped to where it shipped
counter columns (comment_count)
← the alternative is COUNT(*) on
every page view
reporting tables, materialised views
← rebuilt on a schedule
The first two aren’t really denormalisation. A price at time of order and a customer’s current price are different facts, and storing both is correct. Most “denormalisation” in a well-designed ecommerce schema is actually this — recognising it saves the argument.
The cost you take on
duplicated fact
→ must be updated in every copy
→ or must be accepted as a snapshot
who updates it? a trigger, application
code, or a scheduled job
what if it fails halfway?
what reconciles the divergence?
Answer those three before duplicating anything. A denormalised counter with no reconciliation job drifts, and nobody notices until the number is obviously absurd.
The analytics exception
Warehouses invert the advice entirely:
OLTP (transactional) OLAP (analytical)
normalised denormalised
many small writes few huge reads
joins are cheap enough joins across
billions of rows
are not
→ star schema:
wide fact tables,
dimension tables
The same data is modelled differently for the two jobs, which is precisely why a warehouse is a separate system rather than a replica — Warehouse-First Analytics, SQL vs NoSQL.
The practical position
Normalise by default; denormalise with a reason and a plan. Starting normalised and relaxing where measurement demands it is straightforward. Starting denormalised and trying to normalise later means backfilling data that has already diverged, which is a data-cleaning project rather than a migration — Database Migrations.