When would you choose normalisation over denormalisation, and vice versa?
sql-mid-004
Your answer
Answer as you would in a real interview — explain your thinking, not just the conclusion.
Model answer
Normalisation (typically 3NF) eliminates redundancy and update anomalies by decomposing data into smaller tables. It is the right default for transactional systems where data integrity and write correctness matter most — billing, inventory, user accounts. The tradeoff is that reads may require many joins. Denormalisation deliberately introduces redundancy to reduce join cost for read-heavy workloads. I reach for it in reporting databases, data warehouses, or when profiling shows that a join is a consistent bottleneck. A middle ground is materialised views or read replicas that present a denormalised projection of a normalised source. I always normalise first and denormalise only when I have evidence — a query plan or latency metric — that it is needed.
Code example
-- Normalised: products and categories are separate
SELECT p.name, c.name AS category
FROM products p
JOIN categories c ON c.id = p.category_id
WHERE p.is_active = TRUE;
-- Denormalised: category name duplicated in products table
-- (no join needed, but updates to category name require two tables)
SELECT name, category_name
FROM products
WHERE is_active = TRUE;
-- Materialised view: precomputes the join, refreshed periodically
CREATE MATERIALIZED VIEW product_with_category AS
SELECT p.id, p.name, c.name AS category_name
FROM products p JOIN categories c ON c.id = p.category_id;
REFRESH MATERIALIZED VIEW CONCURRENTLY product_with_category;Follow-up
How do materialised views help you get the benefits of both? What are the consistency tradeoffs when you use them?