Denormalization Basics
Denormalization intentionally introduces redundancy into a normalized schema to improve read performance. It trades data integrity and storage for faster queries. Common in analytics, data warehouses, and read-heavy applications.
Denormalization Techniques
- Add redundant columns to avoid JOINs: store user_name in orders
- Pre-computed aggregates: store order_count in users table
- Derived columns: store total_price where price × quantity is frequently queried
- Duplicated lookup data: store category_name instead of category_id
- Flat tables: one wide table instead of many narrow joined tables (data warehouses)
- When to use: read-heavy workloads, reporting, analytics, cached data
Denormalization Examples
-- Normalized: need JOIN to get customer name
SELECT o.id, u.name FROM orders o JOIN users u ON o.user_id = u.id;
-- Denormalized: customer_name stored in orders (redundant but fast)
CREATE TABLE orders_denorm (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id),
customer_name VARCHAR(100), -- redundant! copy from users
total_amount DECIMAL(10,2),
order_count INT DEFAULT 0 -- pre-aggregated
);
-- Fast read (no JOIN needed):
SELECT id, customer_name, total_amount FROM orders_denorm;
-- Risk: if user changes name, you must update orders_denorm too!
-- Solution: trigger or application logic
-- Summary/aggregate table (pre-computed analytics)
CREATE TABLE user_stats AS
SELECT
user_id,
COUNT(*) AS total_orders,
SUM(total_amount) AS lifetime_value
FROM orders
GROUP BY user_id;
-- Rebuild this daily or on each order - trade consistency for speed
-- Real-world example: Elasticsearch denormalizes
-- JSON documents contain all related data flat:
-- { order_id: 1, customer_name: "John", product_names: ["Laptop"] }Quick Quiz
Tip
Tip
Practice Denormalization Basics in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Eliminate redundancy - each fact stored once. 3NF covers most real-world needs.
Common Mistake
Warning
A common mistake with Denormalization Basics is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of Denormalization Basics from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- Denormalization intentionally introduces redundancy into a normalized schema to improve read performance.
- Add redundant columns to avoid JOINs: store user_name in orders
- Pre-computed aggregates: store order_count in users table
- Derived columns: store total_price where price × quantity is frequently queried