Normalization vs deliberate denormalization

Store each fact once so no write can leave two versions; copy a fact for reads only with a path that keeps the copy right.

Before you read: predict

ExamHall's admin console. session_proctors holds one row per proctor per exam session, keyed by (session_id, proctor_id):

session_id | proctor_id | proctor_email
S-11       | p7         | rui@uni.test
S-12       | p7         | rui@uni.test
S-12       | p8         | ana@uni.test
S-14       | p7         | rui@uni.test
S-15       | p9         | lee@uni.test

Rui changes his email on the S-12 session page, which runs:

UPDATE session_proctors SET proctor_email = 'rui.lopes@uni.test'
  WHERE session_id = 'S-12' AND proctor_id = 'p7';
SELECT DISTINCT proctor_email FROM session_proctors WHERE proctor_id = 'p7';

What does the SELECT return?

The 30-second version

Normalization stores each fact once, keyed by what it describes. A repeated fact invites anomalies: an update misses a copy, a fact has no row, a delete takes it along. Denormalizing copies or totals a fact for reads; it stays correct only while an owned path updates every copy: the same transaction, a trigger, or a rebuild job.

Store each fact once so no write can leave two versions; copy a fact for reads only with a path that keeps the copy right.

How it works

Step 1 of 15

One fact, one place. Normalization stores each fact in one row of the table keyed by what it describes; others refer to that key.

In the kitchen

one master card per ancestor, filed by the ancestor's number → one row per fact, keyed by its subject (steps 1, 7)

Analogy: A family-history society's master cards

The secretary keeps one master card per ancestor. Each family branch keeps a folder of copies for quick reading.

In the society	In the database
one master card per ancestor, filed by the ancestor's number	one row per fact, keyed by its subject (steps 1, 7)
a birth year written on every branch's page instead of one master card	a part-key fact repeated per row (step 2)
a correction made on one branch's page only: two birth years in circulation	update anomaly (step 3)
an ancestor noted only in a branch folder: no folder, nowhere to write; folder discarded, ancestor lost	insert and delete anomalies (steps 4–5)
a reunion's attendance list records that day; later corrections do not rewrite it	a snapshot, not a copy (step 8)
folder copies re-copied at the sitting that corrects the master	a copy kept in the same transaction (steps 9–10)
the annual recheck of every folder against the master cards	a drift check and a rebuild (steps 13–14)

In real terms: each fact lives in one row; every extra copy is a promise that some named path updates it.

Breaks down when: a member who sees two birth years asks which is right. A database returns whatever its rows hold and never flags the contradiction. Wrong inference: "the database will notice the copies disagree."

Contrasting case: a society that lends no copies at all. Every reader consults the master cards, so nothing can disagree, but every reader queues at the secretary's desk.

Examples, run for real

4.1 ExamHall: re-grading question 7

EXH-2140: attempts.total_score copies the sum of each attempt's answers.points. Question 7 was re-graded: every answer that chose C gains one point.

4.1 shipped job (answers only):        attempts whose total differs from their answers: 3 of 5 (a1, a3, a4)
4.1 MR !2440 (recount in the same tx): attempts whose total differs from their answers: 0 of 5

The shipped job updated the answers only, so three totals drifted until a check compared them (steps 10, 14). MR !2440 recomputes the totals inside the re-grade's own transaction.

4.2 Proof domain, checkout: one table, three anomalies

orders(order_id, customer_id, customer_email) is keyed by order_id, but the email describes the customer.

4.2 update: order 502's email edited · c-1 now has 2 emails: ines.r@shop.test, ines@shop.test
4.2 insert: new customer c-4 with no order · error 23502 (null in order_id)
4.2 delete: c-3's only order cancelled · rows still holding c-3's email: 0

One edit leaves two emails, a new customer cannot be stored, and a cancelled order takes an email with it (steps 3–5). A customers table keyed by customer_id removes all three (step 7).

4.3 Proof domain, checkout: a trigger-kept count

carts.item_count is kept by a row-level trigger that recounts the cart's lines.

4.3 one INSERT of 3 lines: trigger runs 3 · item_count 3
4.3 one DELETE of 1 line:  trigger runs 1 · item_count 2

The count stays right, and each row written runs the trigger once more (step 11).

Picture it

A grey-haired man holds a large illustrated master card while slipping a small copy into one of two open folders on a blue desk; another card lies flat. — An illustration to help it stick. The mechanism is in the sections above.

Try it

Answer first, then check. Nothing is saved.

Question 1 of 5

A shop keeps its order lines in one table keyed by (order_id, sku):

order_id | sku   | qty | unit_price | product_name
1001     | MUG-2 | 2   | 8.00       | Stone mug
1001     | TEE-9 | 1   | 15.00      | Logo tee
1002     | MUG-2 | 1   | 8.00       | Stone mug
1003     | CAP-1 | 1   | 12.00      | Sun cap

The team wants four things: renaming a product from its product page leaves it one name; a new product can be listed before anyone orders it; cancelling the only order that holds a product leaves that product listed; and the spring repricing leaves each past order's total as it was charged.

Which change meets all four?

Question 2 of 5

A wallet keeps each account's balance as a copy of its ledger's sum, maintained by this trigger:

CREATE FUNCTION sync_balance() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  UPDATE accounts SET balance = (SELECT sum(amount) FROM ledger WHERE account_id = NEW.account_id)
    WHERE id = NEW.account_id;
  RETURN NEW;
END $$;
CREATE TRIGGER ledger_balance AFTER INSERT ON ledger FOR EACH ROW EXECUTE FUNCTION sync_balance();

Account A-3's ledger holds +100, +40 and −30, and its balance reads 110. A top-up runs, in one session:

BEGIN;
INSERT INTO ledger (account_id, amount) VALUES ('A-3', 25);
SELECT balance FROM accounts WHERE id = 'A-3';
INSERT INTO fees (account_id, amount) VALUES ('A-3', 0);
ROLLBACK;
SELECT balance FROM accounts WHERE id = 'A-3';
SELECT sum(amount) FROM ledger WHERE account_id = 'A-3';

The fee insert fails the fees table's CHECK (amount > 0).

What do the three reads return, in order?

Question 3 of 5

The checkout team is adding one jsonb column to its schema, and three uses are proposed for it.

Which use fits a jsonb column best?

Question 4 of 5

A box office keeps events.seats_sold as a copy of each event's ticket count:

CREATE FUNCTION count_seat() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE events SET seats_sold = seats_sold + 1 WHERE id = NEW.event_id;
  ELSE
    UPDATE events SET seats_sold = seats_sold - 1 WHERE id = OLD.event_id;
  END IF;
  RETURN NULL;
END $$;
CREATE TRIGGER tickets_count AFTER INSERT OR DELETE ON tickets FOR EACH ROW EXECUTE FUNCTION count_seat();

Last night a refund script ran ALTER TABLE tickets DISABLE TRIGGER tickets_count, deleted the refunded tickets, and stopped. This morning:

event | seats_sold | tickets
E1    | 40         | 40
E2    | 52         | 40
E3    | 35         | 15
E4    | 18         | 10
E5    | 25         | 25

The first sale after the fix will be a ticket for E3.

Which fix makes the box office's figures right once that sale is made, and keeps them right as later sales arrive?

Question 5 of 5

A music service's chart page lists every track's total streams, highest first. Streams are written only by the nightly import: one transaction whose single INSERT ... SELECT adds that day's rows to plays(track_id, day, streams). The page is read all day. The team wants three things: the page's query reads no row of plays; the page shows the new totals as soon as the import commits; and the import's insert runs no extra statement for each row it adds.

Which design meets all three?

Go deeper

Pro tips

Write the drift check the day you add a denormalized copy: one query that lists every row whose copy differs from its source.
```
SELECT a.id, a.total_score, coalesce(sum(n.points), 0) AS from_answers
FROM attempts a LEFT JOIN answers n ON n.attempt_id = a.id
GROUP BY a.id, a.total_score
HAVING a.total_score IS DISTINCT FROM coalesce(sum(n.points), 0);
```
When: any copy kept by more than one write path (steps 10, 14). Verify: drift check before the re-grade: none · after it: a1 total 7 vs answers 8, a2 total 5 vs answers 6. Status: RAN.
To refresh a materialized view without blocking its readers, give it a unique index and use REFRESH MATERIALIZED VIEW CONCURRENTLY. When: a leaderboard or report is read while it refreshes (step 12). Verify: REFRESH ... CONCURRENTLY without a unique index: error 55000 · with one: ok. Status: RAN (the refusal without an index; reader blocking is not measured, since PGlite has one session), plus DOC: the PostgreSQL 18 REFRESH MATERIALIZED VIEW page.

Source

PostgreSQL behaviour is version-specific; the pack ran PostgreSQL 18.3 in PGlite 0.5.8, and cites the PostgreSQL 18 manual. Valid as of 2026-10-01.

E. F. Codd, A Relational Model of Data for Large Shared Data Banks, CACM 13(6), 1970: https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf (codd-1970.txt, the PDF's text as pdftotext extracts it). Quoted: "There is, in fact, a very simple elimination procedure, which we shall call normalization." and "In one approach the system checks for possible inconsistency whenever an insertion, deletion, or key update occurs. Naturally, such checking will slow these operations down." Supports steps 1 and 14.
W. Kent, A Simple Guide to Five Normal Forms in Relational Database Theory, CACM 26(2), 1983: http://www.bkent.net/Doc/simple5.htm (kent-1983.txt). Quoted: "If at some point in time there are no parts stored in the warehouse, there may be no record in which to keep the warehouse's address." and "There is no obligation to fully normalize all records when actual performance requirements are taken into account." Supports steps 2–6 and 9. Teaching only: it keys no item, because the curriculum row does not name it.
PostgreSQL 18, 8.14 JSON Types: https://www.postgresql.org/docs/18/datatype-json.html (pg-json.txt). Supports step 15.
PostgreSQL 18, 37.1 Overview of Trigger Behavior: https://www.postgresql.org/docs/18/trigger-definition.html (pg-trigger-def.txt). Quoted: "a trigger is executed as part of the same transaction as the statement that triggered it, so if either the statement or the trigger causes an error, the effects of both will be rolled back." Supports step 11.
PostgreSQL 18, 39.3 Materialized Views: https://www.postgresql.org/docs/18/rules-materializedviews.html (pg-matview.txt), and REFRESH MATERIALIZED VIEW: https://www.postgresql.org/docs/18/sql-refreshmaterializedview.html (pg-refresh.txt). Quoted: "the data is not always current; yet sometimes current data is not needed." and "REFRESH MATERIALIZED VIEW completely replaces the contents of a materialized view." Supports step 12 and tip 2.
PostgreSQL 18, 41.10 Trigger Functions (pg-plpgsql-trigger.txt) and CREATE TRIGGER (pg-createtrigger.txt): https://www.postgresql.org/docs/18/plpgsql-trigger.html, https://www.postgresql.org/docs/18/sql-createtrigger.html. Support the TG_OP, NEW/OLD and DEFERRABLE constraint-trigger syntax the scripts run.
Not fetched: Kleppmann, Designing Data-Intensive Applications, ch. 2 (a book named by the curriculum row; no text copy was fetched), so it keys nothing.

Checked against the tools and docs as of 2026-10-01.

Predict first