Explain normalization and denormalization. When would you use each?

Official answer

Normalization structures data around its dependencies to avoid inconsistent repeated facts. For example, products can hold the current product description while order lines reference the product ID. This reduces the number of places that must be updated when that current description changes.

Denormalization deliberately stores redundant or derived data for a use case. A maintained daily-sales summary may avoid repeatedly aggregating billions of order lines. It introduces a refresh/correction responsibility: decide whether readers may see stale totals, how late refunds are applied and how summaries are rebuilt and reconciled.

Not every repeated-looking value is redundant. A purchased unit price on an order line is a historical transaction fact and should not change when the current catalog price changes. Model that meaning before applying a normalization rule.

Prefer a clear authoritative representation, enforce constraints and measure the workload. Add denormalized forms when their benefit justifies storage, write amplification and consistency complexity. A normalized schema does not guarantee correctness by itself, and a denormalized schema does not guarantee speed. Test equivalent results and document which representation is authoritative during failures or backfills.