When to denormalise
🏠The everyday picture
A bank works out your balance from every transaction, but also prints the current balance in your passbook so it doesn't have to add up ten years of entries each time you ask. The printed balance is a deliberate, carefully maintained copy.
📘What it really is
- Denormalisation means deliberately storing a copy or a calculated value, for a reason:
- historical facts: the price on an order line, the address an order was shipped to (Module 53, Lesson 3). These aren't really duplicates: they record what was true at that moment;
- performance: a stored
order_totalorcustomer_order_countso a busy page doesn't recalculate it every time; -
reporting: separate summary tables built for analysis (Level 8's data topics).
-
Rules for safe denormalisation:
- normalise first; denormalise only when you can name the reason (and for performance, measure first, Module 57);
- decide which value is the source of truth;
- keep copies updated in the same transaction as the source (Module 57), or recalculate them on a schedule;
-
document every deliberate copy in
schema.md. -
Most applications should be in 3NF with a few documented exceptions.
🖱️Try it
List every column in your CRM that stores a copy or calculated value. For each, mark it as a historical fact, a performance copy, or an accidental duplicate. Remove the accidental ones; document the rest with their source of truth.
✅Quick check
Is storing the shipping address on an order a normalisation mistake?
Show the answer
No. It's a historical fact: where that order was sent, even if the customer moves later.
What must you decide before storing a calculated total?
Show the answer
Which value is the source of truth, and how the copy is kept up to date.