One Company, Two Names

Copy an organization's name onto every profile that references it and a rename must rewrite all 666,666 copies where the normalized schema rewrites 1. Interrupt that update halfway and one company splits into two — Facebook 333,333, Meta 333,333 — an inconsistency the normalized schema cannot represent.

Concept

Copy a human-meaningful value — an organization's name — onto every row that refers to it, and you've denormalized: the data reads fast and self-contained, but the name now lives in a million places instead of one. Rename the organization and you must find and rewrite all million copies, atomically, or the database ends up holding two names for the same company. You will rename "Facebook" to "Meta", watch the denormalized update touch 666,666 rows where the normalized one touches 1, then interrupt it halfway and watch a single company split into two — an inconsistency the normalized schema simply cannot represent.

Provenance

Designing Data-Intensive Applications, 2nd ed. (Kleppmann & Riccomini, 2025), Chapter 3 — Data Models and Query Languages, §"Trade-offs of normalization" (and §"Many-to-One and Many-to-Many Relationships"):

In a denormalized representation … it creates a headache if we ever need to change the [value], because we now need to find all the occurrences of the old [value] and update them.

The book argues for referencing an organization by ID rather than copying its name. Here you reproduce the exact headache — and the inconsistency it invites — then close it with the normalized schema.

Prerequisites

The surprise is about where a shared fact lives. These help; each line notes where it shows up.

1. Normalization vs. denormalization — normalized keeps each fact in one place and references it by key; denormalized copies it wherever it's used.
2. Many-to-one relationships — many profiles refer to one organization; the org's name is a fact about the org, not about each profile.
3. Update anomaly — when a duplicated fact is updated in some copies but not others, the database holds contradictory values for one real-world thing.
4. Atomicity — an update either fully happens or not at all. Across a million rows, a crash mid-update leaves a partial state; across one row it can't.

Mental model

The same data, two schemas:

• Normalized — profiles_norm(id, person_name, org_id) referencing organizations(id, name, logo_url). The org's name is stored once.
• Denormalized — profiles_denorm(id, person_name, org_name, org_logo_url), with the org name (and logo) copied onto every profile.

Facebook (org 1) renames to Meta. We measure the blast radius of that rename in each schema, then interrupt the denormalized update partway and ask the database how many names the company now has. The whole exercise is one script, code/denormalization_anomaly.sql.

Setup

docker run -d --name ddia-ch3 -e POSTGRES_PASSWORD=study -e POSTGRES_DB=study postgres:16
sleep 3
docker cp denormalization_anomaly.sql ddia-ch3:/tmp/
docker exec ddia-ch3 psql -U postgres -d study -f /tmp/denormalization_anomaly.sql

Do not read the output yet — make each prediction first.

What you should see

• Rename blast radius: 1 row (normalized) vs 666,666 (denormalized).
• Interrupted denormalized update → the company has two names (Facebook 333,333 / Meta 333,333).
• Normalized: all 666,666 profiles read one consistent name; the anomaly is unreachable.

Why

A many-to-one relationship — many profiles, one organization — has a natural home for the org's name: the organization. Normalizing puts it there and lets every profile reference it by ID, so the name exists exactly once. Denormalizing copies that one fact into every profile that mentions it. Copies read faster (no join), but a fact stored in 666,666 places has 666,666 chances to disagree with itself.

Renaming exposes both costs. The write cost is the blast radius: one row vs. two-thirds of a million, with the larger update taking proportionally more write-ahead log, more locking, and a much longer window in which something can go wrong. And the correctness cost is the update anomaly: because the update spans so many rows, it is only as atomic as the transaction wrapping it, and any partial application leaves contradictory copies with no arbiter. A single-row update has no such window — one row is updated atomically or not at all — so the normalized schema cannot even represent "half-renamed." That is precisely why the book argues for referencing an organization by ID: not merely to save space, but because a single source of truth is the only thing that can't contradict itself.

Go deeper

• Wrap it in a transaction. Redo the partial update inside BEGIN … and then ROLLBACK. Predict what the "how many names?" query returns afterward — atomicity turns the fragile 666,666-row update back into all-or-nothing.
• The forgotten column. Rename only org_name and leave org_logo_url pointing at the old logo. Predict how you'd even detect this drift in the denormalized schema — and why it can't happen in the normalized one.
• Measure the write cost. Wrap each rename in \timing and a pg_current_wal_lsn() check. Predict the ratio of WAL bytes generated by the 666,666-row update vs the 1-row update.

Sources: DDIA 2e, Ch. 3, §"Trade-offs of normalization" and §"Many-to-One and Many-to-Many Relationships" · update anomalies are standard normalization theory.