Normalization vs Denormalization
Normalization removes duplicate facts to protect integrity; denormalization duplicates selected facts to speed reads. The right balance depends on write rules and query shape.
Definition
Normalization organizes data so each fact is stored once and dependencies flow from keys, reducing update anomalies. Denormalization deliberately duplicates or precomputes facts to reduce joins and read cost, accepting that every copy must be kept consistent.
Context and scope
The debate appears whenever a query becomes slow or a report needs many joins. Treating it as a style preference hides the real question: which invariants must hold, and what does it cost to keep every copy correct?
Common misconceptions
- Normalized schemas are always slower, so production systems should denormalize everywhere.
- Denormalization means abandoning integrity, when it usually means choosing an explicit owner and propagation mechanism.
Anti-patterns
- Duplicating a mutable field without a trigger, transaction, or reconciliation job that keeps copies aligned.
- Normalizing to the fifth normal form a read-heavy dashboard that could use a maintained summary table.
Related concepts
Related examples
Authoritative source
https://learn.microsoft.com/en-us/office/troubleshoot/access/database-normalization-description
How it works
Normalization applies rules that eliminate redundancy. First normal form requires atomic values. Second normal form removes partial dependencies on a composite key. Third normal form removes transitive dependencies, so a non-key attribute depends only on the key. Higher forms address subtler anomalies. The payoff is a single source of truth: changing a customer’s name updates one row, and no report can disagree.
Denormalization reverses part of that work on purpose. You may copy a customer name onto an order, store a comment count on a post, or flatten parent and child rows into one document. Each copy is a cache of a fact owned elsewhere, and every write must update it or schedule reconciliation.
A concrete trade-off
An orders table joined to customers and order_items might need three joins to render a page. At high read volume, a covering index or a summary table can remove those joins. But if the customer address changes, the order history should often keep the old address anyway, so duplication is semantically correct rather than a compromise. Distinguish immutable snapshots, which are safe to copy, from mutable attributes, which need propagation.
When to use which
Normalize transactional paths where correctness and write amplification matter: balances, inventory, permissions. Denormalize read paths with stable access patterns, or when a materialized view or search index can be rebuilt from the source. Measure first; most small systems do not need either extreme.
Trade-offs and pitfalls
Normalized models reduce update anomalies but increase join cost and query complexity. Denormalized models read quickly but multiply write cost and create drift when a path forgets a copy. A trigger, transactional outbox, or scheduled rebuild can maintain copies, yet each adds operational surface. The dangerous case is a duplicated mutable fact with no owner: eventually two screens show different values and nobody knows which is right.