Denormalization

Why deliberately put back the duplication you spent so much effort removing?

A team spends weeks decomposing a schema so that every fact is stated exactly once. Then, six months later, they copy the customer's name onto the order table, and store an order total that could have been computed from the lines.

If duplication was the disease, this is the team reinfecting themselves on purpose. Either the earlier work was misguided or something has changed. Nothing about the facts changed — so what did?

Go back to what the decomposition actually bought. It made certain contradictions unrepresentable: with the city stored once, no update sequence can produce two cities for one customer. That guarantee is real, and it is bought with a currency worth naming precisely. What is the currency?

Joins. Every fact moved to its proper home is a fact you must go and fetch when you want it alongside others. So the schema that makes writing safest makes reading most expensive, and it does so systematically — the more thoroughly you separate the subjects, the more pieces a question must reassemble.

Now ask an empirical question about a particular system, not a general one: how often is each fact written, and how often read? For most applications the answer is wildly lopsided. An order's customer name is written once, at order time, and read on every list, every receipt, every search result — thousands of times to one. Notice what that ratio does to the trade. The guarantee protects against a hazard that arises on write; the cost is levied on read. When reads outnumber writes by three orders of magnitude, you are paying a large, continuous cost to insure against a rare event.

So the honest reframing is this: normalization and denormalization are not right and wrong. They are two ends of a dial between write-side safety and read-side cost, and the setting depends on a measurable property of your workload. That is why the answer changes without the facts changing.

But be careful, because there are two quite different things people call denormalization and only one of them is a pure trade.

The first is derived data: storing the order total that could be recomputed from the lines. The stored value is redundant, but its source is unambiguous — if they disagree, the lines are right and the total is stale. Detecting and repairing that is mechanical.

The second is copied data: writing the customer's name onto the order. Here you must think harder, because the naive reading — "this is now a stale copy" — is often wrong. Consider: when a customer changes their name, should last year's invoice change? Almost certainly not. The invoice is meant to record what was true at the time. That copy is not redundant at all; it is a point-in-time fact that the normalized schema was quietly failing to record. Recognising this changes the design decision entirely, because the copy now has an independent justification and there is nothing to keep in step.

Where the copy really is meant to track the original, you have taken on an obligation the database no longer enforces for you, and you must say how it will be met: a trigger, a materialized view the engine refreshes, a background reconciliation, or an accepted staleness window. The failure mode is not choosing badly among those. It is choosing none of them, so the copy silently drifts and nobody knows which value is authoritative — exactly the shredded-truth problem normalization removed.

One caveat about evidence: the read-side gain from removing a join is real but varies enormously with the engine, the indexes, and whether the joined rows were already in memory. A well-indexed join on a warm cache can be cheap enough that the duplication buys almost nothing. Measure before trading a guarantee away.

The distinction that does the most work here is between a copy and a snapshot, and it is worth stating as a question you can ask of any duplicated column: if the original changes, should this change too? If yes, you have a copy, and you owe the system a refresh mechanism. If no, you have a historical fact that deserved its own column all along, and calling it denormalization is a misnomer.

The second correction is about sequence. Denormalization is a considered retreat from a design you understand, not a substitute for doing the analysis. If you never worked out which facts belong to which subject, your redundancy is not a trade — it is just an unmapped set of places where the database can contradict itself. Normalize to think; denormalize to measure up to a workload.

And keep the direction of the pressure in view. Systems drift towards more duplication over time, because each individual case is defensible and no single one is where the trouble appears. Writing down, next to each redundant column, what refreshes it is the cheap habit that stops that drift from becoming unexplained data.

• Materialized views, and how engines refresh them incrementally rather than by full rebuild.
• Why point-in-time attributes on invoices are a modelling requirement, not a redundancy.
• How wide-column and document stores push this trade to its extreme by design.

Every term the collection defines is gathered in the glossary.