The relational model

You have rows and columns (F1). The obvious move is to put everything you know into one big convenient table. Almost every serious data system refuses to do that — and the reason why is one of the most important ideas in the field.

Where we are

Lesson 3 of Part 0 · Foundations. F1 gave us the shape of data — records, fields, types. This lesson asks the first hard design question: once you have many kinds of records that refer to each other, how do you arrange them? The answer — the relational model — underlies essentially every operational system on earth, and it sets up F3, where we'll see where these tables physically live.

The question

Suppose you're tracking 200 wedding guests, and each guest is staying at a hotel. The tempting design is a single table where every guest row also carries their hotel's full name and address — everything in one place, no fuss. Why does nearly every database textbook tell you not to do this? Why split data into many tables joined by IDs instead of one big convenient table with everything in it?

Intuition — the wedding guest list

You start the convenient way. Next to all 200 guests you write the full hotel name and street address. It feels efficient — everything you'd want is right there on each row.

Then the hotel changes its address. Now you must find and fix 200 rows. You will miss one. The moment you do, your list says the hotel is at two different addresses at once — and nothing in the data tells you which is correct. You've created two conflicting "truths." This is called an update anomaly, and it is the quiet killer of hand-built spreadsheets everywhere.

The fix is to stop repeating the hotel. Keep a guests list and a separate hotels list. Each hotel is written down once, with its own short ID. Each guest stores only that ID — a tiny reference, not the whole address. Change the address in one place and all 200 guests instantly point at the corrected fact. The shared ID is how the two lists "hold hands."

Mechanics — keys and references, stated precisely

The relational model formalizes "lists that hold hands" with two ideas:

Primary key (PK) — a column (or set of columns) whose value uniquely identifies a row in its table. No two rows share it; it is never empty. hotel_id is the primary key of the hotels table; it is the table's permanent name for each hotel.
Foreign key (FK) — a column in one table that holds the primary-key value of a row in another table. It does not copy the data; it points at it. The guest's hotel_id is a foreign key referencing hotels.hotel_id. A foreign key is literally how a relationship is encoded — "this guest stays at that hotel" becomes "this guest row's FK equals that hotel row's PK."

The database can enforce this: a foreign key value must match some existing primary key (referential integrity). That guarantee — you can't point at a hotel that doesn't exist — is something a flat spreadsheet can never give you.

Two small tables, one shared ID

Here is the design in full. The hotel's address lives in exactly one place; each guest carries only the ID.

  hotels                                    guests
  ─────────────────────────────────────     ─────────────────────────────
  hotel_id │ name        │ address          guest_id │ name   │ hotel_id
  (PK)     │             │                  (PK)     │        │ (FK)──┐
  ─────────┼─────────────┼──────────────    ─────────┼────────┼───────┤
   H1      │ Grand Plaza │ 5 River Rd        G1       │ Aanya  │  H1 ◀─┘
   H2      │ Bayview Inn │ 12 Ocean Ave      G2       │ Ben    │  H1
                                             G3       │ Chen   │  H2
            ▲                                              the FK points
   one row per hotel — the address                        back at a hotel's
   is written down exactly ONCE                            primary key

The hotel address appears once, not once-per-guest. Three guests, two of them at H1, and "5 River Rd" is stored a single time. Change it there and Aanya and Ben are both corrected at once — no anomaly is even possible, because there's no second copy to fall out of sync.

Intuition — one fact, one home

The whole philosophy in one sentence: every fact should live in exactly one place. "The Grand Plaza is at 5 River Rd" is one fact. It should be recorded once, owned by one row, and referred to from everywhere else. If a fact lives in two places, sooner or later the two places disagree, and then you don't have data — you have an argument.

Mechanics — normalization, 1NF → 3NF

Normalization is the systematic procedure for arranging columns into tables so that each fact lives in exactly one place. It proceeds in stages called normal forms. The intuition matters more than the formal definitions:

1NF — each cell holds one atomic value, not a list. No "H1, H2" crammed into a single field; split it into rows. (Recall F1: one fact per cell.)
2NF — every non-key column depends on the whole primary key, not just part of it. (Matters mostly when the PK is several columns.)
3NF — every non-key column depends directly on the key, never by way of another non-key column (no transitive dependencies). The hotel address is really a fact about the hotel_id, not the guest, so it must not ride along in the guests table. The mnemonic: each column depends on "the key, the whole key, and nothing but the key" — which is exactly what banishes the address from all 200 guest rows.

Working tables that are "in 3NF" have, by construction, removed the redundancy that causes update anomalies. You rarely recite these forms on the job — but every time you split a repeating fact into its own table with an ID, you are doing 3NF by hand.

Putting it back together: the JOIN

Splitting data raises an obvious worry: a report needs each guest with their hotel address, but we deliberately stored those apart. The relational model's answer is to recombine them at query time with a join — match each guest's foreign key to the hotel row sharing that primary key.

  guests ⋈ hotels  ON guests.hotel_id = hotels.hotel_id
  ───────────────────────────────────────────────────────────
  guest_id │ name   │ hotel_id │ name        │ address
  ─────────┼────────┼──────────┼─────────────┼──────────────
   G1      │ Aanya  │  H1      │ Grand Plaza │ 5 River Rd
   G2      │ Ben    │  H1      │ Grand Plaza │ 5 River Rd
   G3      │ Chen   │  H2      │ Bayview Inn │ 12 Ocean Ave
                              ▲ reassembled on demand — the wide,
                                convenient view we WANTED all along

Notice the join output is the big convenient table we were tempted to build in the first place — "5 River Rd" now appears twice. The difference is decisive: this wide view is derived, computed fresh from the single sources of truth. Nobody updates it directly, so it can never go stale or self-contradict. We get the convenience of one big table for reading, while keeping the safety of normalized tables for writing.

The relationship, made concrete

"A guest stays at a hotel" was never stored as prose — it's the FK→PK match. Joining is just following those links. One hotel relates to many guests (a one-to-many relationship), so the join naturally repeats the hotel across all its guests. This is the everyday plumbing of relational systems.

The core trade-off

So we have two ways to lay out the same information, and they optimize opposite things. This trade-off is the heart of practical data engineering.

	Normalized (split tables + IDs)	Denormalized (one wide pre-joined table)
Each fact stored	Once, in one place	Many times, copied across rows
Update a fact	One row, instant, no anomaly	Every copy — slow, error-prone, risks conflict
Read it back	Must join at query time (work per read)	Already assembled — pure sequential scan
Optimizes for	Correctness + write efficiency	Read / scan speed
Natural home	Operational systems (lots of small writes)	Analytics + training (few writes, huge reads)

Intuition — normalize to write, denormalize to read

Normalization is how you keep the truth tidy while it's changing — guests check in and out, hotels update details, edits happen constantly and must stay consistent. Denormalization is what you do when the truth has stopped changing and you just need to read all of it, fast: flatten the joins once into a wide table so a reader can scan straight through with no lookups. You normalize for the life of the data and denormalize for its consumption.

Mechanics — denormalization is a deliberate, derived copy

Denormalization is intentionally re-introducing redundancy — pre-joining normalized tables into a wide table — to avoid paying join cost on every read. The catch is that you've recreated the very duplication normalization removed, so the wide table can go stale. The discipline that makes it safe: the wide table is never the source of truth and is never edited in place. It is regenerated from the normalized tables by a repeatable pipeline (recall F0 — "the pipeline is the product"). The normalized side owns correctness; the denormalized side is a disposable, rebuildable read cache.

Why post-training data looks denormalized

This is fundamental general data engineering — it is not specific to machine learning. But it explains a pattern you'll meet constantly in post-training, so it's worth seeing the connection now.

The operational sources behind a post-training dataset are deeply relational, and for exactly the reasons above. Consider how the data is born (the regimes from lesson 01):

A prompt has many responses (one-to-many).
A response has many pairwise human judgments from different annotators (one-to-many).
Each record has exactly one source / license — provenance tracked once and referenced, never copied (the lineage concerns of lesson 03).

An annotation platform is a live operational system: judgments stream in, prompts get revised, a license gets corrected. It must be normalized — store the license once, fix it once, never let two rows disagree about where a record came from. A foreign key is what ties a judgment to its response and a response to its prompt.

  HOW IT'S STORED (operational, normalized)        HOW IT'S TRAINED ON (flat, denormalized)
  ─────────────────────────────────────────       ──────────────────────────────────────────
  prompts ──one-to-many──▶ responses               ┌────────────────────────────────────────┐
     │  pk: prompt_id          │  pk: response_id   │ prompt_text │ chosen │ rejected │ source │
     │                         │  fk: prompt_id     ├────────────────────────────────────────┤
     ▼                         ▼                    │  …one self-contained training row…       │
  sources/licenses        judgments (pairwise)      │  …another self-contained row…            │
     pk: source_id            fk: response_id       └────────────────────────────────────────┘
                              fk: source_id              ▲ joins already resolved; just scan
       ▲ each fact once,                                   front-to-back, no lookups, GPU-fed
         edits stay consistent

Yet the trainer never does transactional lookups. It performs enormous sequential scans — stream millions of self-contained examples through the GPUs, in order, as fast as the disk allows. Stopping to join three tables per example would starve the accelerators. So the pipeline does the normalize → denormalize move: data originates normalized (for correct, consistent collection) and is consumed as a flat, wide, denormalized table (for raw read throughput). The training file is exactly the "join output" from earlier — a derived, rebuildable wide view, not the source of truth.

This thread continues

The machinery that performs that normalize→denormalize flattening is the join logic returning in lesson 05 (transformation), and the duplication it deliberately creates is exactly what lesson 06 (dedup & decontamination) has to police. And where the normalized side and the denormalized side physically live — transactional stores (OLTP) versus scan-optimized stores (OLAP) — is the whole subject of F3, next.

Takeaway

Don't put everything in one big table, because repeating a fact lets its copies disagree — an update anomaly. The relational model stores each fact once, gives each row a primary key, and links rows with foreign keys; normalization (through 3NF) is the discipline of "one fact, one home." A join recombines the pieces on demand. The grand trade-off: normalize for correctness and cheap writes, denormalize for fast reads. Post-training data is born normalized in annotation and provenance systems, then deliberately flattened into wide training tables — because trainers do massive sequential scans, not lookups. Next: where these two kinds of tables physically live.