Normalization vs Denormalization

When to store each fact once for correctness and when to copy it for fast reads: normal forms, the anomalies they prevent, denormalization techniques, and how to keep copies in sync.

Overview

Normalization means designing tables so that each fact is stored in exactly one place. A customer's email lives in the customers table, and every order refers to the customer by ID. Change the email once and every query sees the new value.

Denormalization means deliberately storing a fact more than once, or storing something you could compute, so that a common read needs fewer joins or less work. An order row might carry a copy of the customer's name; a post might keep a comment_count column instead of counting comments on every page view.

Neither approach is better in general: normalization protects correctness and makes writes simple; denormalization makes specific reads cheap and pushes the cost onto writes, which now have to keep every copy right. Most real systems do both: a normalized source of truth, plus a few carefully maintained copies for the hottest reads.

What you will be able to do

  • Spot update, insert and delete anomalies and name the normal form that removes them.
  • Normalize a small schema to third normal form and explain BCNF.
  • Pick a denormalization technique (copied column, counter, summary table, materialized view, read model) for a read problem.
  • Explain how to keep copies in sync, synchronously or with change data capture, and what can go wrong.
  • Adapt the reasoning to document stores and analytics warehouses.

Key concepts

  • Update anomalies
  • Functional dependency
  • 1NF, 2NF, 3NF, BCNF
  • Precomputed columns
  • Materialized views
  • Change data capture
  • Fan-out on write
  • Access-pattern design
  • Star schema

Interview questions

  • What is normalization, and what problems does it solve?
  • Explain 1NF, 2NF, 3NF and BCNF with examples.
  • When should you denormalize, and what does it cost?
  • How do materialized views help, and what are their limits in PostgreSQL?
  • How do you keep denormalized data consistent across systems?
  • Fan-out on write or fan-out on read for a news feed?
  • How does schema design differ in DynamoDB or MongoDB?
  • Why are data warehouses denormalized when application databases are not?

Sign up free to read the full topic

More System Design topics

Start free on Cariara · Coding problems · System design studio