Normalization vs Denormalization

The most fundamental lever in schema design

Normalization and denormalization are the two ends of the single most fundamental lever in schema design: you are trading write integrity against read performance, and every schema sits somewhere on that line.

Normalization: each fact exactly once

Third normal form (3NF), the practical target, says every non-key column depends on the key, the whole key, and nothing but the key. A product's name and price live in one products row; an order_items row references that product by product_id rather than copying the name and price. The payoff is write integrity: change the product name in one place and every order that references it sees the new name, with zero risk of two rows disagreeing. Normalized schemas make writes cheap and correct, and they make update anomalies (the same fact stored in two rows that drift apart) structurally impossible.

The cost is joins on read. Joins are perfectly fine when they are indexed and bounded: an index on order_items.order_id and a primary-key lookup on products turns a 3-table join into a handful of B-tree seeks, and Postgres or MySQL will serve that in single-digit milliseconds even at hundreds of millions of rows. Joins fail to scale in two situations. First, when the join fan-out is large and unbounded (joining a user to all of their events across years). Second, and this is the one that actually forces the issue, when the tables live on different shards: a cross-shard join means a scatter-gather across the network, and that does not scale. Once your data is sharded, you must co-locate or denormalize.

Denormalization: pay at write time, on purpose

Denormalization means deliberately storing a copy of a fact where it is read, to avoid a join or a cross-shard lookup on a hot read path. For a read-heavy order-history page you precompute a row that already contains the product name, the quantity, the line total, and the order status, so rendering the page is a single indexed range scan with no joins. The cost is symmetrical: you now have copies to keep in sync, so a product rename becomes a fan-out write that must touch every denormalized copy, and if you miss one you get an update anomaly. You have moved the pain from read time to write time, which is the right trade only when reads vastly outnumber writes.

Interview nuance: the strong answer never says "denormalize for performance" in the abstract. It names the specific query, the read/write ratio, and the scale trigger: "this order-history query runs 20k times per second, product data changes maybe once a day, so I denormalize the display fields into the order row and accept a rare backfill on rename."

The managed middle ground is a materialized view (or a summary table). You keep the source of truth normalized, and the database maintains a precomputed, denormalized copy for you, refreshing it on a schedule or incrementally. You get join-free reads without hand-writing fan-out logic, at the cost of some staleness. Daily revenue rollups, leaderboards, and dashboard aggregates are the classic use.

Schema

• order_id
• user_id
• status

• order_id
• product_id
• qty

• product_id
• name
• price

• order_id
• user_id
• statuscopy
• product_namecopy
• qtycopy
• line_totalprecomputed
• created_at

• orders1-norder_items— join on read: indexed, single-digit ms
• products1-norder_items— each fact exactly once
• orders1-norder_history_rows— refreshed as a materialized view
• products1-norder_history_rows— rename = fan-out write to every copy

Recap: normalize by default for write integrity, denormalize only for a specific hot read path with a real read/write ratio and scale trigger (especially to dodge cross-shard joins), and reach for materialized views when you want join-free reads without hand-maintaining the copies.