# DDIA Ch. 3: one company, two names

## 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.

### Where to learn them

- **Normal forms & anomalies (#1, #3):** any database course on normalization
  (update/insert/delete anomalies); DDIA §"Trade-offs of normalization."
- **Atomicity (#4):** DDIA Ch. 8 §"Atomicity" — the guarantee that makes the
  one-row update safe and the million-row update fragile.

If only one is new, make it #3 — the update anomaly is what you reproduce.

## Environment

Exact, reproducible row counts:

- macOS 26.5.2 (Darwin 25.5.0), arm64
- Docker 29.6.2 running the official `postgres:16` image → PostgreSQL 16.14
- 2,000,000 profiles spread evenly across 3 organizations (~666,666 each)

## 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.

## Steps

### Step 1 — the blast radius (predict, then observe)

**Predict.** Facebook renames to Meta. In each schema, how many rows does the
rename have to update?

**Observe.**

```
===== Facebook renames to Meta. Blast radius of the rename: =====
--- normalized: update the one organization row ---
UPDATE 1
--- denormalized: update every profile that copied the name ---
UPDATE 666666
```

**1 row vs. 666,666.** The normalized name is a single fact in a single row. The
denormalized name is copied onto every profile, so the rename must rewrite all
666,666 of them — more WAL, more locks, a longer transaction.

### Step 2 — interrupt it (the anomaly)

**Predict.** That 666,666-row update runs as one big transaction. Suppose it's
interrupted — a crash, a timeout, a buggy `WHERE` — and only half the rows get
the new name. What does the denormalized table now say the company is called?
And can the normalized schema ever end up in that state?

**Observe.**

```
===== Now the anomaly: an interrupted denormalized update (only half commits) =====
--- denormalized: how many distinct names does this ONE company now have? ---
 org_name | count
----------+--------
 Facebook | 333333
 Meta     | 333333
```

**Two names for one company.** Half the profiles say Facebook, half say Meta —
the denormalized table is now internally contradictory, and nothing in the schema
flags it as wrong. There is no single source of truth to consult; each copy is
equally "the name."

### Step 3 — the normalized schema can't reach that state

**Observe.**

```
===== The normalized schema cannot reach that state =====
--- normalized: the name lives in one row; every profile reads it via join ---
 company_name | profiles
--------------+----------
 Meta         |   666666
```

All **666,666** normalized profiles read **Meta**, consistently — because the
name lives in *one* row that they reference. Renaming is a single-row update,
which is atomic by construction: it either happened or it didn't. There is no
half-renamed state to land in.

## 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.

**The boundary — denormalization is a deliberate trade, not a mistake.** The
normalized schema pays a join on every read; the denormalized schema pays this
maintenance burden on every write to a shared value. When the shared value almost
never changes and reads are hot (a product's category, a country code), copying
it can be the right call — *if* you own the process that keeps the copies in sync
and wrap multi-row updates in a transaction. The book's framing holds:
denormalized data is "a form of derived data" that needs a maintenance process;
skip the process and you get exactly the two-named company above. Normalize by
default; denormalize on purpose, with the sync mechanism in hand.

### Go deeper

1. **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.
2. **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.
3. **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.