Introduction
Denormalization is the deliberate process of introducing redundancy into a normalized schema to improve read performance, usually by reducing the number of JOINs required for common queries. While normalization (1NF-3NF) minimizes redundancy and prevents anomalies, it can require many JOINs across tables for reporting or read-heavy workloads, which becomes expensive at scale. Denormalization trades some write-side complexity and storage space for faster reads, and is a common technique in reporting databases, data warehouses, and high-traffic read paths of OLTP systems.
Cricket analogy: A broadcaster keeps a duplicated 'career stats' summary next to each player's live scorecard instead of recalculating from every match ever played, trading extra storage and update effort for instant on-screen stats during a live broadcast.
Syntax
-- Normalized (3NF): requires a JOIN to get customer city with each order
SELECT o.order_id, o.order_date, c.customer_city
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;
-- Denormalized: customer_city duplicated directly on the orders table
CREATE TABLE orders_denormalized (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
customer_city VARCHAR(100) NOT NULL, -- redundant copy, avoids JOIN
order_date DATE NOT NULL,
total_amount DECIMAL(10,2) NOT NULL
);Explanation
By storing customer_city directly on the orders_denormalized table, a report that groups sales by city no longer needs to JOIN against customers — it reads a single table. The cost is that customer_city is now duplicated across every order for that customer, and if a customer moves to a new city, every historical order row would need to be updated (or the redundancy is accepted as a point-in-time snapshot rather than a live reference). Common denormalization techniques include duplicating columns across tables, storing precomputed aggregates (e.g., a running total_orders count on the customers table), and building wide, flattened reporting tables (star/snowflake schemas in data warehouses).
Cricket analogy: Storing a player's 'home ground' directly on every match record avoids joining to a players table for a venue report, but if the player transfers teams, old match rows still show the outdated home ground unless explicitly updated.
Example
-- Precomputed aggregate: avoid recalculating COUNT(*) on every page load
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100) NOT NULL,
total_orders INT NOT NULL DEFAULT 0, -- denormalized aggregate
lifetime_spend DECIMAL(12,2) NOT NULL DEFAULT 0
);
-- Kept in sync via application logic or a trigger whenever an order is placed
UPDATE customers
SET total_orders = total_orders + 1,
lifetime_spend = lifetime_spend + 149.99
WHERE customer_id = 42;Analysis
Reading a customer's order count becomes an O(1) lookup on customers instead of a COUNT(*) scan/aggregation over orders, which matters a great deal at high read volume. The trade-off is that total_orders and lifetime_spend must be kept consistent with the orders table on every insert, update, or delete — via application code, triggers, or scheduled batch jobs — introducing a risk of the denormalized value drifting out of sync with the source of truth. Denormalization should be applied selectively, after profiling shows that JOIN or aggregation cost is a genuine bottleneck, not as a default design choice.
Cricket analogy: Reading a batsman's career century count from a stored field on their profile is instant, instead of scanning every innings ever played; but that count must be updated via a trigger after every match, and applying this shortcut everywhere without profiling risks stale numbers.
Key Takeaways
- Denormalization intentionally duplicates data to reduce JOINs and speed up reads.
- It trades write complexity and storage for read performance.
- Common patterns: duplicated columns, precomputed aggregates, flattened reporting/star-schema tables.
- Denormalized data requires extra effort (triggers, application logic, batch jobs) to stay consistent.
Practice what you learned
1. What is the primary goal of denormalization?
2. Which of the following is a common denormalization technique?
3. What is the main risk introduced by denormalization?
4. In which scenario is denormalization most commonly applied?
Was this page helpful?
You May Also Like
Normalization: 1NF to 3NF
Learn the step-by-step process of eliminating data redundancy and anomalies through the first three normal forms.
Query Optimization Basics
Foundational techniques for writing queries the optimizer can execute efficiently.
Indexing Strategies and Tradeoffs
How to choose composite index column order, use covering indexes, and know when not to index.