10.3 Denormalization on Purpose — The Complete SQL Course

Normalization is the default because it protects writes: one fact, one place. Denormalization is what you do when you deliberately copy or store a derived fact because a read path needs it — and you accept the maintenance contract out loud.

In 10.2, normalization cleaned up a messy spreadsheet. This lesson keeps that discipline, then bends it on purpose for a café dashboard that asks the same daily sales question all day long.

Normalized source of truth first

Start with the write-friendly shape. Tickets point at branches. Line items point at tickets and menu items. The line total is a computed column: PostgreSQL stores the derived value, but the expression tells the database how to maintain it whenever the row is written.

CREATE TABLE cafe_branches (
  branch_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  branch_name text NOT NULL UNIQUE
);
 
CREATE TABLE cafe_tickets (
  ticket_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  branch_id integer NOT NULL REFERENCES cafe_branches(branch_id),
  opened_at timestamptz NOT NULL
);
 
CREATE TABLE menu_items (
  menu_item_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  item_name text NOT NULL UNIQUE,
  category text NOT NULL
);
 
CREATE TABLE ticket_lines (
  ticket_id integer NOT NULL REFERENCES cafe_tickets(ticket_id),
  line_no integer NOT NULL,
  menu_item_id integer NOT NULL REFERENCES menu_items(menu_item_id),
  quantity integer NOT NULL CHECK (quantity > 0),
  charged_unit_price numeric(5,2) NOT NULL CHECK (charged_unit_price >= 0),
  line_total numeric(7,2) GENERATED ALWAYS AS (quantity * charged_unit_price) STORED,
  PRIMARY KEY (ticket_id, line_no)
);
 
INSERT INTO cafe_branches (branch_name)
VALUES ('center'), ('harbor');
 
INSERT INTO menu_items (item_name, category)
VALUES
  ('Latte', 'coffee'),
  ('Cold Brew', 'coffee'),
  ('Cookie', 'pastry');
 
INSERT INTO cafe_tickets (branch_id, opened_at)
VALUES
  (1, TIMESTAMPTZ '2026-06-10 08:05:00+00'),
  (1, TIMESTAMPTZ '2026-06-10 09:15:00+00'),
  (2, TIMESTAMPTZ '2026-06-10 10:20:00+00'),
  (2, TIMESTAMPTZ '2026-06-11 08:40:00+00');
 
INSERT INTO ticket_lines (ticket_id, line_no, menu_item_id, quantity, charged_unit_price)
VALUES
  (1, 1, 1, 2, 4.50),
  (1, 2, 3, 1, 3.00),
  (2, 1, 2, 1, 4.75),
  (3, 1, 1, 1, 4.50),
  (3, 2, 3, 2, 3.00),
  (4, 1, 2, 2, 4.75);
 
SELECT tl.ticket_id,
       mi.item_name,
       tl.quantity,
       tl.charged_unit_price,
       tl.line_total
FROM ticket_lines AS tl
JOIN menu_items AS mi
  ON mi.menu_item_id = tl.menu_item_id
ORDER BY tl.ticket_id, tl.line_no;

CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 2
INSERT 0 3
INSERT 0 4
INSERT 0 6
 ticket_id | item_name | quantity | charged_unit_price | line_total
-----------+-----------+----------+--------------------+------------
         1 | Latte     |        2 |               4.50 |       9.00
         1 | Cookie    |        1 |               3.00 |       3.00
         2 | Cold Brew |        1 |               4.75 |       4.75
         3 | Latte     |        1 |               4.50 |       4.50
         3 | Cookie    |        2 |               3.00 |       6.00
         4 | Cold Brew |        2 |               4.75 |       9.50
(6 rows)

This is still normalized where it matters. A ticket line stores the charged price because that is an order-time fact. It references menu_items for reusable product identity. The computed line_total is a small denormalization with a low maintenance cost because the database owns the calculation.

A summary table for a read-heavy question

Now suppose the operations dashboard constantly asks: "sales by branch by day." The normalized answer is a join plus a group every time. That is fine until the question is hot enough that you want a cached answer.

A summary table is a denormalized table whose rows are precomputed from source tables:

CREATE TABLE branch_day_sales (
  branch_name text NOT NULL,
  sales_day date NOT NULL,
  tickets integer NOT NULL,
  line_count integer NOT NULL,
  gross_sales numeric(9,2) NOT NULL,
  PRIMARY KEY (branch_name, sales_day)
);
 
INSERT INTO branch_day_sales (branch_name, sales_day, tickets, line_count, gross_sales)
SELECT cb.branch_name,
       CAST(ct.opened_at AS date) AS sales_day,
       count(DISTINCT ct.ticket_id) AS tickets,
       count(*) AS line_count,
       sum(tl.line_total) AS gross_sales
FROM cafe_tickets AS ct
JOIN cafe_branches AS cb
  ON cb.branch_id = ct.branch_id
JOIN ticket_lines AS tl
  ON tl.ticket_id = ct.ticket_id
GROUP BY cb.branch_name, CAST(ct.opened_at AS date);
 
SELECT branch_name, sales_day, tickets, line_count, gross_sales
FROM branch_day_sales
ORDER BY sales_day, branch_name;

CREATE TABLE
INSERT 0 3
 branch_name | sales_day  | tickets | line_count | gross_sales
-------------+------------+---------+------------+-------------
 center      | 2026-06-10 |       2 |          3 |       16.75
 harbor      | 2026-06-10 |       1 |          2 |       10.50
 harbor      | 2026-06-11 |       1 |          1 |        9.50
(3 rows)

That table is faster to read because the join and aggregate already happened. It is also more dangerous than the normalized source because it can go stale. The professional move is not "never do this." The professional move is "write down how this table is refreshed, and prove it matches the source."

Verify the cache against the source

At creation time, the summary table should match the normalized source exactly. This query recomputes the source summary and counts rows that differ in either direction:

WITH source_summary AS (
  SELECT cb.branch_name,
         CAST(ct.opened_at AS date) AS sales_day,
         count(DISTINCT ct.ticket_id) AS tickets,
         count(*) AS line_count,
         sum(tl.line_total) AS gross_sales
  FROM cafe_tickets AS ct
  JOIN cafe_branches AS cb
    ON cb.branch_id = ct.branch_id
  JOIN ticket_lines AS tl
    ON tl.ticket_id = ct.ticket_id
  GROUP BY cb.branch_name, CAST(ct.opened_at AS date)
),
source_minus_cache AS (
  SELECT branch_name, sales_day, tickets, line_count, gross_sales
  FROM source_summary
  EXCEPT
  SELECT branch_name, sales_day, tickets, line_count, gross_sales
  FROM branch_day_sales
),
cache_minus_source AS (
  SELECT branch_name, sales_day, tickets, line_count, gross_sales
  FROM branch_day_sales
  EXCEPT
  SELECT branch_name, sales_day, tickets, line_count, gross_sales
  FROM source_summary
)
SELECT (SELECT count(*) FROM source_minus_cache)
       + (SELECT count(*) FROM cache_minus_source) AS mismatched_rows,
       (SELECT count(*) FROM branch_day_sales) AS summary_rows;

 mismatched_rows | summary_rows
-----------------+--------------
               0 |            3
(1 row)

That 0 is the contract check. If new tickets arrive and the summary table is not refreshed, this query stops returning zero. Section 11.1 will teach views and materialized views properly; for now, read a materialized view as a database-managed summary query that stores its result and can be refreshed. Same idea, different tool shape.

OLTP tables and OLAP shapes

The normalized ticket tables are OLTP, short for online transaction processing: many small writes, each protecting business facts at the right grain. The summary table leans OLAP, short for online analytical processing: read-heavy reporting over facts that are already recorded.

Analytics schemas often go further into a star schema: one central fact table for measurable events, surrounded by dimension tables for descriptive context. In this cafe, ticket_lines is starting to look like a fact table: it has quantities, charged prices, and links to branch, item, and date context. Section 17.4 gives star schemas their real lesson; here the preview is simple: normalized OLTP shapes protect writes, while OLAP shapes make repeated reads easier.

sql — playgroundlive

CREATE TABLE cafe_branches (
branch_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
branch_name text NOT NULL UNIQUE
);

CREATE TABLE cafe_tickets (
ticket_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
branch_id integer NOT NULL REFERENCES cafe_branches(branch_id),
opened_at timestamptz NOT NULL
);

CREATE TABLE menu_items (
menu_item_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
item_name text NOT NULL UNIQUE,
category text NOT NULL
);

CREATE TABLE ticket_lines (
ticket_id integer NOT NULL REFERENCES cafe_tickets(ticket_id),
line_no integer NOT NULL,
menu_item_id integer NOT NULL REFERENCES menu_items(menu_item_id),
quantity integer NOT NULL CHECK (quantity > 0),
charged_unit_price numeric(5,2) NOT NULL CHECK (charged_unit_price >= 0),
line_total numeric(7,2) GENERATED ALWAYS AS (quantity * charged_unit_price) STORED,
PRIMARY KEY (ticket_id, line_no)
);

INSERT INTO cafe_branches (branch_name)
VALUES ('center'), ('harbor');

INSERT INTO menu_items (item_name, category)
VALUES
('Latte', 'coffee'),
('Cold Brew', 'coffee'),
('Cookie', 'pastry');

INSERT INTO cafe_tickets (branch_id, opened_at)
VALUES
(1, TIMESTAMPTZ '2026-06-10 08:05:00+00'),
(1, TIMESTAMPTZ '2026-06-10 09:15:00+00'),
(2, TIMESTAMPTZ '2026-06-10 10:20:00+00'),
(2, TIMESTAMPTZ '2026-06-11 08:40:00+00');

INSERT INTO ticket_lines (ticket_id, line_no, menu_item_id, quantity, charged_unit_price)
VALUES
(1, 1, 1, 2, 4.50),
(1, 2, 3, 1, 3.00),
(2, 1, 2, 1, 4.75),
(3, 1, 1, 1, 4.50),
(3, 2, 3, 2, 3.00),
(4, 1, 2, 2, 4.75);

CREATE TABLE branch_day_sales AS
SELECT cb.branch_name,
     CAST(ct.opened_at AS date) AS sales_day,
     count(DISTINCT ct.ticket_id) AS tickets,
     count(*) AS line_count,
     sum(tl.line_total) AS gross_sales
FROM cafe_tickets AS ct
JOIN cafe_branches AS cb
ON cb.branch_id = ct.branch_id
JOIN ticket_lines AS tl
ON tl.ticket_id = ct.ticket_id
GROUP BY cb.branch_name, CAST(ct.opened_at AS date);

SELECT branch_name, sales_day, tickets, line_count, gross_sales
FROM branch_day_sales
ORDER BY sales_day, branch_name;

-- break it: uncomment — source rows still obey foreign keys
-- INSERT INTO ticket_lines (ticket_id, line_no, menu_item_id, quantity, charged_unit_price)
-- VALUES (99, 1, 1, 1, 4.50);

⌘/Ctrl + Enter to run

Denormalization is powerful when you can name the source, the duplicate, and the refresh rule. Next, 10.4 looks at schema antipatterns: shapes that usually start as shortcuts but do not come with an honest maintenance contract.

Checkpoint

Answer all three to mark this lesson complete

Denormalization on Purpose

Normalized source of truth first

A summary table for a read-heavy question

Verify the cache against the source

OLTP tables and OLAP shapes

Checkpoint ✦

Checkpoint