21 / 32 · 4 min
When to stop normalizing
Normalization is not a score to maximise. Every split you make is a join you pay for on every read, and past a point that trade stops being worth it.
A fully normalized schema for one order page might need six joins: order, customer, address, line items, products, prices. If that page loads ten thousand times an hour and the data changes twice a day, six joins on every read is the wrong trade.
Denormalization is deliberately storing a fact twice for speed. It is not a mistake: it is a decision, and the difference is whether you know what you are giving up.
Common and legitimate:
- A stored total.
orders.totalalongside the line items that produce it. Recomputing it on every read is wasteful; the risk is that they disagree. - A copied name.
order_items.product_namefrozen at purchase time. This is not redundancy at all: the product's name today is a different fact from its name when the order was placed. - A counter.
posts.comment_countrather thanCOUNT(*)over a million comments.
The cost is always the same: two places to update, and the possibility they disagree. Every denormalization needs a plan for keeping them in step:
| What you copy | Kept in step by | What breaks without it |
|---|---|---|
orders.total | The transaction that writes the items | A total that does not match its own lines |
order_items.product_name | Nothing, because it is frozen on purpose | Renaming a product silently rewrites history |
posts.comment_count | A trigger, or a nightly recount | A count that drifts a little every week |
Takeaway: normalize until it is correct, denormalize where you have measured that it is slow, and never denormalize without deciding how the copies stay in step.