Denormalization

Denormalization is the deliberate storage of the same fact in more than one place, or of data already joined or summarized, so that reading it is simpler or faster. It is the reverse of normalization, which stores each fact once to prevent update and deletion anomalies.

Why it exists

Normalization is not free. William Kent’s 1983 guide to the normal forms is explicit that its rules lean toward the assumption that fields are updated frequently, and that they tend to penalize retrieval: data that could be read from one record in an unnormalized design may have to be read from several. His own example is finding the addresses of all warehouses stocking a part, which takes one table in the unnormalized design and two in the normalized one. Denormalization buys back that retrieval cost.

The forms it takes

• Copied attributes. A product’s category name stored on every product row, or a customer’s region stored on every order.
• Pre-joined tables. A wide table combining orders, customers, and products so a report needs no joins.
• Stored derived values. An order total kept alongside its lines, or a daily summary table kept alongside the detail.

Two different failures: a changed grain and a lost detail

The three forms above are usually discussed as one trade — storage and update cost against fewer joins. There is a second difference that matters more once anything reads the table, and it cuts across those categories rather than along them: whether the grain survived.

Most denormalization keeps it. Copying a many-to-one attribute does: putting the customer’s region on every order row adds a column, there is still one row per order, and any question the original could answer the wide table can still answer. Pre-joining on a many-to-one relationship keeps it too, as long as every row finds its match or the join keeps unmatched rows: an inner join silently drops the orders whose customer is missing. Even a stored derived value keeps it, provided the detail it was derived from is still there — an order total next to its lines forecloses nothing.

Two cases break something, and they break different things. Neither is a category from the list.

• A pre-join on a one-to-many relationship changes the grain. A parent row now appears once per child, so counting rows no longer counts parents and summing a parent-level column double-counts it: an order of 100 with two lines contributes 200 to a naive SUM, and grouping by the order key before summing still gives 200. The parent’s value is recoverable by taking one value per parent — MAX or MIN of the repeated amount, grouped by the parent key — or from the original parent table, and the number of parents by counting distinct parent keys. Whether anything was lost depends on the join: an inner join drops parents with no children, so an order with no lines is simply absent and no query over the result brings it back, while a left join keeps it with empty child columns. What is gone in every case is the ability to trust plain row-level arithmetic.
• A summary that replaces the detail loses it. The columns it aggregated away are not in the table in any form, so no query recovers them.

The distinction is worth keeping because the fixes differ: fan-out is an arithmetic hazard you correct in the query, provided no rows were dropped, while a replacing summary — or a join that dropped unmatched rows — is a loss you cannot undo later.

A replacing summary is the clearest irreversible case, alongside the dropped rows of an inner join. A table of daily totals per product cannot answer a question about customers, because the customer column no longer exists in any form — and no query, index, or engine recovers it. Concretely, from order detail you can compute revenue by day, by customer, by region, by product, and any combination. From (date, product, total) you can compute revenue by day and by product. The rest is gone.

Two consequences follow. A pre-aggregated table should be an addition to the detail, not a replacement for it, unless you have decided which questions will never be asked — a decision people are bad at making in advance. And a distinct count is worse than a sum here: daily unique customers cannot be added into a monthly figure at all, so a summary at the wrong grain does not merely limit the questions, it produces wrong answers to ones that look answerable. Whether a stored aggregate can be combined further is exactly the additivity question, and it has to be settled before the summary becomes something reports depend on.

Where it is reasonable, and where it is risky

In an analytical platform, denormalization is usually the intended design. Tables are typically rebuilt by a pipeline rather than edited row by row by many code paths, and a model people can read without a diagram is worth the redundancy. Dimensional models repeat descriptive attributes by design for that reason.

In a transactional database, the same move is riskier. Copies are updated by application code, one transaction at a time, by many code paths. Any path that changes one copy and not the other — a new feature, a bulk fix, a second service — creates exactly the inconsistency normalization was preventing, and nothing reports it.

Keeping copies consistent

Denormalization moves the job of consistency from the schema to something else, and that something has to be named:

• in a database, updating every copy inside one transaction, or maintaining the copy with a trigger or materialized view;
• in a pipeline, deriving every copy from one definition, rebuilding the affected tables together, and publishing them atomically — through a transaction spanning the tables or a version pointer that readers switch to in one step — so a change lands everywhere or nowhere;
• in both, a test that checks copies agree, run before results are published rather than after a complaint.

A practical rule for operational systems: measure the read cost first. Indexes, caching, or a separate read model can remove the need, and a denormalized copy that nobody maintains deliberately tends to become a second, slightly different version of the truth. How operational and analytical models make this trade differently is worked through in Operational and Analytical Models: Designed for Different Jobs, and the rules being relaxed are covered in database normalization.

Further reading

• William Kent, A Simple Guide to Five Normal Forms in Relational Database Theory (1983)