What is denormalization and when would you use it?
Denormalization deliberately adds redundant data, such as copied columns or precomputed totals, to avoid joins and speed up reads. It trades storage and write complexity for read performance, and suits read-heavy reporting.
Explain it simply
A normalized design is like keeping each fact in one notebook and looking it up when needed. Denormalization is photocopying a few facts onto the page you read most, so you do not keep flipping between notebooks. It is faster to read, but when the original changes you must update every copy.
Example
Normalized: orders(id, customer_id, ...) and customers(id, name). Showing an order list needs a join for the name.
Denormalized: store customer_name directly in orders, or keep order_count in customers.
-- precomputed counter kept up to date by the application or a trigger
ALTER TABLE customers ADD COLUMN order_count INT DEFAULT 0;Good reasons:
- Read-heavy dashboards or reports where joins across millions of rows are slow.
- Data warehouses, where star schemas intentionally repeat dimension data.
- Historical snapshots, such as copying the product price at order time. This one is also correct, since the price on that day is a different fact from today's price.
Risks: inconsistent copies, more complex writes, more storage. Normalize first, measure, then denormalize only the proven hot spots.
What to say in the interview
"Denormalization duplicates data to cut joins and speed reads. I use it for reporting or measured bottlenecks, and I own the cost of keeping copies consistent."
Common mistakes
- Denormalizing early, before any evidence of a performance problem.