Beginner LevelSystem Design & Architecture
When and why should you Denormalize relational databases?
❓ Interview Prompt: Explain Database Denormalization, duplicating fields to eliminate JOIN overhead, improving read latency, and managing write update maintenance.
Database Denormalization is the intentional technique of adding redundant data fields to a normalized schema:
Trade-Off:
•
Read Latency: Eliminates expensive multi-table 'JOIN' operations by embedding pre-computed columns (e.g. storing 'user_name' directly inside the 'orders' table).
•
Write Penalty: Updating 'user_name' requires mutating multiple tables simultaneously, increasing write maintenance complexity.
•
Rule: Denormalize only when read latency on high-frequency queries is a critical bottleneck.
Code Implementation Example
sql
-- Denormalized Orders table embedding customer_name directly
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
user_name VARCHAR(100) NOT NULL, -- Redundant copy eliminating JOINs on reads!
total_amount DECIMAL(10, 2)
);💡Denormalizing order tables to eliminate multi-table JOINs on read.
🎯 Interview Key Takeaway
Denormalize relational tables selectively to optimize high-frequency read queries by eliminating multi-table JOINs.
#System Design#Denormalization#Databases#Performance#Beginner
Back to all System Design & Architecture Questions→