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 does too. 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. Nothing is lost — every key and column is still there — but 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. The information is recoverable by aggregating over the parent key first, or by counting distinct parents; what is gone 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, and a replacing summary is a decision you cannot undo later.

The second is the irreversible one. 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 and rebuilding the affected tables together, 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.

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