When denormalising is reasonable
45 min · Advanced
Need help? Ask a tutor on WhatsApp (opens in a new tab)A report page joins five tables on every visit and has become slow. Good design says store each fact once, yet here a stored copy could help. When is breaking that rule worth it?
The cost of many joins
Denormalisation is storing a copy or a pre-computed value on purpose, so frequent reads need fewer joins or less calculation. It gives up some of "each fact once" for speed, so something must keep the copy correct.
A join costs work every time a query runs: MySQL finds the matching rows in each table and combines them. The query below lists orders with their totals from four normalised tables.
Example 1 · A normalised order list: four tables and a SUM
-- "My orders" page for customer 1: number, date, status, items and billSELECT o.order_id, o.order_date, o.status, COUNT(*) AS items, SUM(oi.quantity * oi.unit_price) AS billFROM customers cJOIN orders o ON o.customer_id = c.customer_idJOIN order_items oi ON oi.order_id = o.order_idJOIN products p ON p.product_id = oi.product_idWHERE c.email = 'asha.rao@example.com'GROUP BY o.order_id, o.order_date, o.statusORDER BY o.order_date DESC;+----------+------------+-----------+-------+----------+ | order_id | order_date | status | items | bill | +----------+------------+-----------+-------+----------+ | 10 | 2025-04-12 | SHIPPED | 1 | 4398.00 | | 3 | 2025-02-02 | DELIVERED | 2 | 43839.00 | | 1 | 2025-01-05 | DELIVERED | 2 | 20597.00 | +----------+------------+-----------+-------+----------+
What this code does
- Every time Asha opens the page, MySQL joins four tables and adds up every order line. With three orders that is nothing; with a million customers opening the app, the same sums are computed again and again.
- The
productsjoin is not even needed for these columns. Removing unneeded joins is the first fix to try, before any denormalisation.
For most apps this is fine. Joins on primary and foreign keys use indexes (the Indexes module shows how), and a few thousand rows join in a millisecond or two. Denormalising "to be safe" only adds copies to keep in step. The rule of thumb is: normalise first, measure, and denormalise only the specific query that is proven slow and runs very often, such as a home page, a dashboard or a mobile app's order list.
The signs that a stored copy may be worth it: the same expensive calculation runs thousands of times a minute, the data it reads changes rarely compared with how often it is read, and a slightly stale value would be acceptable or can be kept exact cheaply.
Storing totals and copies on purpose
A stored total is an aggregate saved in a column, such as orders.total_amount, so a read does not add up the lines each time. Like every copy, it must always equal the value computed from its source.
A common denormalisation is a total saved on the order row. The script below adds total_amount and compares it with the sum of the order lines.
Example 2 · A stored total on orders
ALTER TABLE orders ADD COLUMN total_amount DECIMAL(12,2) NOT NULL DEFAULT 0;
UPDATE orders oJOIN (SELECT order_id, SUM(quantity * unit_price) AS bill FROM order_items GROUP BY order_id) t ON t.order_id = o.order_idSET o.total_amount = t.bill;
-- The same page, now with no join and no SUMSELECT order_id, order_date, status, total_amountFROM ordersWHERE customer_id = 1ORDER BY order_date DESC;+----------+------------+-----------+--------------+ | order_id | order_date | status | total_amount | +----------+------------+-----------+--------------+ | 10 | 2025-04-12 | SHIPPED | 4398.00 | | 3 | 2025-02-02 | DELIVERED | 43839.00 | | 1 | 2025-01-05 | DELIVERED | 20597.00 | +----------+------------+-----------+--------------+
What this code does
ALTER TABLEadds the column, and oneUPDATE ... JOINfills it from the order lines. From now ontotal_amountis a copy ofSUM(quantity * unit_price)that must stay equal to it.- The page query reads one table. The cost has moved from every read to every write: each change to
order_itemsmust now also updateorders.total_amount.
Denormalisation usually takes one of three shapes: a stored total (an order's bill amount, a post's like count), a copied column (a customer's name repeated on each order to skip a join) or a reporting table that holds pre-joined, pre-grouped rows for dashboards.
Not every copy is denormalisation. order_items.unit_price looks like a copy of products.price, but it is a different fact: the price on the day of the order. The same goes for a delivery address saved on an order; the customer may move later, but the parcel went to the old address. Those are historical snapshots and belong in a normalised design. A real denormalised copy is one that must always equal its source: if orders.total_amount ever differs from the sum of the order's lines, one of them is wrong.
Keeping copies correct
A trigger is SQL that MySQL runs automatically when a row of a table is inserted, updated or deleted, so no application can forget it. Triggers are one way to keep a stored copy correct.
A stored copy is safe only if every change to its source also changes the copy. The first script adds a trigger for new order lines; the second finds copies that drifted and repairs them.
Example 3 · A trigger keeps the total correct on insert
ALTER TABLE orders ADD COLUMN total_amount DECIMAL(12,2) NOT NULL DEFAULT 0;
UPDATE orders oJOIN (SELECT order_id, SUM(quantity * unit_price) AS bill FROM order_items GROUP BY order_id) t ON t.order_id = o.order_idSET o.total_amount = t.bill;
-- Runs automatically for every new order lineCREATE TRIGGER order_items_after_insertAFTER INSERT ON order_itemsFOR EACH ROW UPDATE orders SET total_amount = total_amount + NEW.quantity * NEW.unit_price WHERE order_id = NEW.order_id;
-- Ravi adds a Rubik's Cube to his PLACED order 15INSERT INTO order_items (order_id, product_id, quantity, unit_price)VALUES (15, 14, 1, 299.00);
SELECT o.order_id, o.total_amount, (SELECT SUM(quantity * unit_price) FROM order_items oi WHERE oi.order_id = o.order_id) AS computedFROM orders oWHERE o.order_id = 15;+----------+--------------+----------+ | order_id | total_amount | computed | +----------+--------------+----------+ | 15 | 5196.00 | 5196.00 | +----------+--------------+----------+
What this code does
NEWis the row just inserted. For each new line, the trigger adds its amount to the parent order, so the stored total and the computed total stay equal: 4,897 + 299 = 5,196.- The trigger body is a single statement, so it needs no
BEGIN ... ENDblock. Because it lives in the database, every app and every script that inserts lines keeps the total right.
Example 4 · Catching drift with a check query, then repairing it
ALTER TABLE orders ADD COLUMN total_amount DECIMAL(12,2) NOT NULL DEFAULT 0;
UPDATE orders oJOIN (SELECT order_id, SUM(quantity * unit_price) AS bill FROM order_items GROUP BY order_id) t ON t.order_id = o.order_idSET o.total_amount = t.bill;
-- A support script fixes a quantity but forgets the totalUPDATE order_items SET quantity = 2 WHERE order_id = 9 AND product_id = 9;
-- Check query: stored total versus the total computed from the linesSELECT o.order_id, o.total_amount AS stored_total, SUM(oi.quantity * oi.unit_price) AS computedFROM orders oJOIN order_items oi ON oi.order_id = o.order_idGROUP BY o.order_id, o.total_amountHAVING o.total_amount <> SUM(oi.quantity * oi.unit_price);
-- Repair: recompute every total from the sourceUPDATE orders oJOIN (SELECT order_id, SUM(quantity * unit_price) AS bill FROM order_items GROUP BY order_id) t ON t.order_id = o.order_idSET o.total_amount = t.bill;
SELECT COUNT(*) AS drifted_ordersFROM orders oWHERE o.total_amount <> (SELECT SUM(quantity * unit_price) FROM order_items oi WHERE oi.order_id = o.order_id);+----------+--------------+----------+ | order_id | stored_total | computed | +----------+--------------+----------+ | 9 | 898.00 | 1397.00 | +----------+--------------+----------+ +----------------+ | drifted_orders | +----------------+ | 0 | +----------------+
What this code does
- The direct
UPDATEchanged a line but nothing changed the order's total, so order 9 now says 898 while its lines add up to 1,397. The check query lists exactly that order. - The repair statement is the same one that first filled the column. Running the check query daily (and the repair when it finds rows) is a cheap safety net next to triggers.
- Same transaction: the application updates the line and the order total together, so both happen or neither does (the Transactions module).
- Trigger: the database updates the stored total itself whenever an order line changes, as in the first script.
- Check query: a query that compares the stored copy with the value computed from the source and lists every row that has drifted.
- Repair or rebuild: an
UPDATE ... JOINthat recomputes every copy from the source, run after a bug or on a schedule.
Order of execution
Add a stored total safely
Put the steps in order.
AFTER INSERT trigger does nothing when a line is updated or deleted, so a full design needs insert, update and delete triggers, or a check query that catches what they miss.Reporting tables and views
A reporting table stores the result of a query as real rows, so a dashboard reads it fast, but it is a copy that must be refreshed. A view, by contrast, is a saved query that stores no data.
A view runs its query every time; a reporting table stores the result. The script below builds both from the same orders data and shows what each one costs.
Example 5 · A view versus a reporting table
-- View: a saved query. Always current, computed on every read.CREATE VIEW monthly_revenue_v ASSELECT DATE_FORMAT(o.order_date, '%Y-%m') AS month, SUM(oi.quantity * oi.unit_price) AS revenueFROM orders oJOIN order_items oi ON oi.order_id = o.order_idWHERE o.status = 'DELIVERED'GROUP BY month;
-- Reporting table: stored rows. Fast to read, refreshed on a schedule.CREATE TABLE monthly_revenue_report ASSELECT month, revenue FROM monthly_revenue_v;
-- Order 11 is delivered after the report was builtUPDATE orders SET status = 'DELIVERED' WHERE order_id = 11;
SELECT v.month, v.revenue AS live_view, r.revenue AS report_tableFROM monthly_revenue_v vLEFT JOIN monthly_revenue_report r ON r.month = v.monthORDER BY v.month;
-- Nightly refreshTRUNCATE TABLE monthly_revenue_report;INSERT INTO monthly_revenue_report SELECT month, revenue FROM monthly_revenue_v;
SELECT month, revenue FROM monthly_revenue_report WHERE month = '2025-04';+---------+-----------+--------------+ | month | live_view | report_table | +---------+-----------+--------------+ | 2025-01 | 22544.00 | 22544.00 | | 2025-02 | 66037.00 | 66037.00 | | 2025-03 | 70186.00 | 70186.00 | | 2025-04 | 20196.00 | 898.00 | | 2025-05 | 4998.00 | 4998.00 | +---------+-----------+--------------+ +---------+----------+ | month | revenue | +---------+----------+ | 2025-04 | 20196.00 | +---------+----------+
What this code does
- Right after order 11 is delivered, the view already shows April at 20,196.00, while the reporting table still shows 898.00 until it is refreshed. That gap is the price of storing results.
- The refresh is two statements: empty the table and fill it again from the query. Dashboards read
monthly_revenue_reportquickly, and the live tables stay normalised.
Some databases have materialised views, which are views whose results are stored and refreshed by the database. MySQL 8 does not have them, so in MySQL a reporting table plus a refresh step (TRUNCATE and INSERT ... SELECT, run nightly or after each batch) does the same job.
Reporting tables are the safest kind of denormalisation. The live tables stay normalised, the dashboard reads the summary, and everyone agrees that the summary is "as of the last refresh".
Next step
Check your understanding
11 quick questions