✏️ Explanatory Question
Level: Expert — Tests whether you understand the real-world trade-off between data integrity and read performance.
Denormalization is the process of intentionally adding redundancy to a normalized database — by merging tables or duplicating data — to improve read performance. It's essentially the reverse of normalization, done deliberately for speed.
| Aspect | Normalization | Denormalization |
|---|---|---|
| Goal | Reduce redundancy | Improve read speed |
| Redundancy | Minimal | Intentional |
| Joins needed | More | Fewer |
| Read performance | Slower (more joins) | Faster |
| Write performance | Faster, simpler | Slower (update duplicates) |
| Data integrity | High | Risk of inconsistency |
| Storage | Less | More |
Because data is duplicated, an update must change it in every location. If one copy is missed, the data becomes inconsistent. This is often managed with triggers, scheduled jobs, or application logic to keep duplicates in sync.
-- NORMALIZED: order_name requires a join to fetch customer name
-- orders(order_id, customer_id, amount)
-- customers(customer_id, customer_name)
SELECT o.order_id, c.customer_name, o.amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;
-- DENORMALIZED: store customer_name directly in orders (no join needed)
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
customer_name VARCHAR(100), -- duplicated for fast reads
amount DECIMAL(10,2)
);
-- Read is faster — no join required
SELECT order_id, customer_name, amount FROM orders;
-- Precomputed aggregate (denormalized summary)
ALTER TABLE customers ADD COLUMN total_orders INT DEFAULT 0;
-- Kept in sync via a trigger or scheduled job