Denormalisation

Intermediate

Schema Design

Strategically add redundant data (precomputed totals, copied columns) to reduce JOIN overhead on read-heavy workloads; balance query speed against write complexity and consistency.

Overview

Normalisation eliminates redundancy for data integrity; denormalisation intentionally reintroduces redundancy to improve read performance. Common techniques include storing precomputed aggregates (order_total on the orders table instead of SUM-ing line items every time), copying frequently-joined columns (customer_name on orders), and using summary tables updated by triggers or application code. Denormalisation is a trade-off: queries are faster, but writes must maintain consistency across all copies. Generated columns (MySQL 5.7+) and materialised-view-style summary tables are controlled ways to denormalise without sacrificing the source of truth.

Stored Aggregates

Precomputing and caching aggregates avoids expensive SUM/COUNT queries on large tables. Use a trigger or application logic to keep the cached value in sync. MySQL generated columns can automate simple expressions.

SQL — trigger-maintained precomputed aggregate

-- Precomputed total on orders table (application updates on each line-item change)
ALTER TABLE orders ADD COLUMN total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00;

-- Trigger to keep total_amount in sync
DELIMITER $$
CREATE TRIGGER trg_update_order_total
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    UPDATE orders
    SET total_amount = (
        SELECT COALESCE(SUM(qty * unit_price), 0)
        FROM order_items WHERE order_id = NEW.order_id
    )
    WHERE id = NEW.order_id;
END$$
DELIMITER ;

Copying Frequently-Joined Columns

Copy a stable, rarely-changing column from a parent table to avoid a join on every read. Customer email/name on an orders row is a common example — the value at time of order is stored, not a live FK lookup.

SQL — snapshot denormalisation for order history

-- Snapshot pattern: copy customer name at order time
CREATE TABLE orders (
    id             BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_id    BIGINT       NOT NULL,
    customer_name  VARCHAR(120) NOT NULL,  -- copied at insert, intentionally stale-safe
    customer_email VARCHAR(255) NOT NULL,  -- audit trail: what was true at order time
    total_amount   DECIMAL(12,2) NOT NULL,
    created_at     DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_customer_id (customer_id)
);

-- Fast report query — no JOIN needed
SELECT customer_name, COUNT(*) AS order_count, SUM(total_amount)
FROM orders
WHERE created_at >= '2026-01-01'
GROUP BY customer_id, customer_name;

Summary Tables

A summary table holds pre-aggregated data rebuilt periodically (batch job, event-driven). It trades real-time accuracy for query speed on reporting workloads.

SQL — summary table for fast reporting

-- Summary table updated nightly by a scheduled job
CREATE TABLE daily_sales_summary (
    sale_date    DATE        NOT NULL,
    category_id  INT         NOT NULL,
    order_count  INT         NOT NULL,
    revenue      DECIMAL(14,2) NOT NULL,
    PRIMARY KEY (sale_date, category_id)
);

-- Dashboard query hits summary table — sub-millisecond even at scale
SELECT sale_date, SUM(revenue) AS daily_revenue
FROM daily_sales_summary
WHERE sale_date >= CURDATE() - INTERVAL 30 DAY
GROUP BY sale_date
ORDER BY sale_date;

Key Points to Remember

1Denormalise only after profiling — premature denormalisation adds complexity for no gain
2Every denormalised copy is a consistency liability; a trigger or event must keep all copies in sync
3Generated columns (STORED or VIRTUAL) automate simple expressions without application code
4Snapshot pattern (copy at insert time) is intentional — preserves historical state, not a bug
5Summary tables trade staleness for speed; suitable for reporting, not operational queries
6Consider MySQL views first — they add no storage; denormalise when views are too slow

Interview Questions

What is the difference between normalisation and denormalisation?

EasyInfosys

When would you choose to denormalise a database schema?

MediumAmazon

How do you maintain consistency when the same data exists in multiple columns?

HardFlipkart

What is a summary table and when is it appropriate?

MediumTCS

How do MySQL generated columns help with controlled denormalisation?

MediumOracle

Ask Aria about Denormalisation

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

Normalisation (1NF–BCNF)

Table Partitioning