Normalization and when to denormalize

SQL, indexing and PostgreSQL Mid-level Python 3.13+ PostgreSQL 17+ Reviewed By Best Latest Apps

Pick the fix

Order history must show the price customers actually paid, even after products are repriced. Schema choice?

Options
Hint 1 of 1

Is the price paid a fact about the product or the order?

Choose an answer, then open Show answer below to check it.

Show answer

Answer: A Store unit_price on each order line at purchase time

Correct. It's a historical fact of the order, not a copy of the current price.

Normalisation removes copies of facts that should stay in sync. The price paid is a separate fact fixed at purchase time, so storing it on the order line is correct, not duplication.

Why not the others?
  • B Join order lines to products for the price. Shows today's price, rewriting history.
  • C Keep a price-history table and join by timestamp. Possible but complex; the order already knows what was charged.
  • D Cache product prices in Redis. Caching current prices doesn't record past ones.

30-second answer

Normalizing (up to third normal form, roughly 'every column depends on the key, the whole key and nothing but the key') stores each fact once, so updates can't leave copies disagreeing. Denormalizing copies or precomputes data to make hot reads cheaper, at the cost of keeping copies in sync. Start normalized, measure, then denormalize specific read paths with a clear owner for the copy: a trigger, the same transaction, or an event.

Practise saying it · 35 s. No recording, just a timer

Explain normalization trade-offs.

Say your answer out loud, then check which of these points you covered:

  • Each fact stored once
  • Update anomalies avoided
  • Denormalize hot reads after measuring
  • Snapshot vs copy
  • Owner for every copy

Deep dive

Snapshot data is not denormalization: an order line should store the price paid, because the product price changes later.

Materialized views are a managed copy: REFRESH MATERIALIZED VIEW CONCURRENTLY needs a unique index.

Counters (likes, comments) are the classic denormalization; update them atomically or recompute periodically.

Read models in CQRS are denormalization moved to a separate store.

Common mistakes

  • Denormalizing before measuring a real read bottleneck.
  • Copying data without deciding which copy is the source of truth.
  • Storing the current product price on order lines by reference, so old orders change.

Senior take & follow-ups

Explain the anomaly normalization prevents, distinguish snapshots from duplication, and denormalize deliberately.

Follow-up questions

Think of your answer first, then open the hint.

  1. What's the risk of a counter column?

    Show hint

    Hot-row contention and drift if any write path forgets it.

  2. When is a materialized view a good fit?

    Show hint

    Expensive aggregates that can be a few minutes stale.

Related questions

Suggest a fix if something here is wrong or out of date.