Lesson 19.519.5 Third normal form in detail: keep it, break it on purpose

About fifteen minutes. Read 19.2 and 19.4 first. This concept starts from zero: what a normal form is, and then the third in depth, with the business data, where an AI system keeps one copy on purpose. It teaches you when to keep the rule and when to break it.

In everyday life. A school prints a class list for every teacher: each student's name, class, and the class teacher's phone number. It saves the teachers a lookup. Then one class teacher changes her phone number. The office updates the staff list, but forty class lists still carry the old number, and nobody knows which ones. The phone number belonged to the teacher, not to the student, and it was copied onto every student's line anyway.

What you will learn. How to find a fact stored in the wrong place, and which copies an AI system keeps on purpose, each with a rule in the database that keeps it true.

Why it matters. A copied fact goes wrong and nothing warns you. Change it in one place, and the other copies keep the old value. That is how one company ends up with two names.

One request, start to finish. A coding agent's first draft of the passages table, before the AP expert saw it, looked like this:

-- BAD: not in third normal form
CREATE TABLE passages_bad (
  id        uuid PRIMARY KEY,
  tenant_id text,
  org_name  text,          -- the company's name, copied onto every passage
  content   text,
  embedding vector(1024)
);

In plain words: a table of policy passages, each with an id (uuid is a long random id), its company's id, its company's name, its text, and its embedding (vector(1024), a list of 1,024 numbers that 19.13 explains). The agent's reason for the name: "so a citation can show the company's name without a second lookup." Here is what happened next, run for real on the lab's database:

1. Cedar Ridge's passages were loaded with org_name = 'Cedar Ridge Supply'.
2. Cedar Ridge renamed itself Cedar Ridge Building Supply. The name was changed in the sign-in server, where names belong.
3. Someone remembered the passages table and updated the rows they could find: one passage, the one they had in front of them.
4. A query asked: how many different names does company org_cedar have in this table? The answer was 2.

One company, two names, and no way to tell which is right. Nothing crashed. The data was simply wrong, in a place nobody looks until a customer does.

What is the one rule?

A fact about something else belongs in that thing's table, once. The phone number was a fact about the teacher, so it belongs on the staff list, once. The company's name is a fact about the company, so it belongs with the company, once. A table that follows this rule everywhere is said to be in third normal form, or 3NF.

To check the rule, one word helps. A column depends on another when knowing the second tells you the first. Knowing a vendor_id tells you the vendor's name, so the name depends on the vendor id. In a good table, every column depends on the row's key, and on nothing else. In passages_bad, the passage's id tells you its tenant_id, and the tenant_id tells you org_name. So org_name depends on the company, not on the passage: it is a fact about something else, stored in the wrong table. A chain like this, key to one column to another, is called a transitive dependency. Store the name on every passage and each rename must find every copy, and one copy is always missed. That is an update anomaly: a change that has to be made in many places, and is bound to miss one. Nothing crashes. The data is simply wrong.

Where have you seen third normal form already?

You have already used a 3NF design. Chapter 18's sign-in tables (people, companies and memberships) follow third normal form strictly: a company's name is stored once, in its own row, and a membership names the company only by its id. This concept is 3NF in detail, for the business data, where an AI system has one good reason to break the rule, and must keep it everywhere else.

For the curious: first and second normal form, and what a key column is, are in field guide F5.

How do you spot a fact in the wrong place?

Read each column and ask: "If I knew only this row's key, would I know this value? Or would I need to know some other column first?" Here are the transitive dependencies the lab's agents and Ana found in Brightline's data, each with where the fact belongs:

• passage → tenant_id → org_name. The company's name belongs to the company, in the sign-in database. Not in the business database at all.
• invoice → vendor_id → vendor_name. An invoice names its vendor by id. The vendor's name lives in vendors, and a query joins to it.
• purchase order → invoice_number → invoice amount. A purchase order has its own amount, the one approved. The invoice's amount lives on the invoice. Copying it onto the purchase order would hide exactly the difference get_purchase_order exists to find.
• passage → document_id → document title. A passage names its document by id. The title lives in policy_documents.
• vendor → routing number → bank name. A bank's name is decided by its routing number, the nine-digit number that names a U.S. bank. Brightline's file carries both. No tool uses the routing number, so the loader never stores it (19.11). With no routing column, nothing in vendor_banking depends on another non-key column.
• passage → content → embedding. A passage's embedding is decided entirely by its text and the model. A copy, kept on purpose (below).

What does the good shape look like?

The company's name lives in one place only, and every other table holds just the company's id:

-- GOOD: third normal form at the company boundary
-- In the sign-in database (Chapter 18), the one home of a company's name:
--   organization (id, name, ...)
-- In the business database, the company is an id and nothing more:
CREATE TABLE tenants (
  id        text PRIMARY KEY,                 -- the same id the sign-in server uses
  loaded_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE policy_passages (
  tenant_id   text NOT NULL,
  passage_id  text NOT NULL,
  document_id text NOT NULL,
  content     text NOT NULL,
  -- ... the embedding, and the columns that guard it, below
  PRIMARY KEY (tenant_id, passage_id),
  FOREIGN KEY (tenant_id, document_id) REFERENCES policy_documents (tenant_id, document_id)
);
-- org_name is in neither table. That is 3NF.

In plain words: tenants holds only each company's id and the time it was loaded. timestamptz is a date and time with its time zone, and DEFAULT now() fills in the current time when a row is added, so nobody has to. A passage holds its company's id, its own id, its document's id and its text. Its key is the pair of company and passage id, and the FOREIGN KEY line says the pair of company and document must already exist in policy_documents.

If the business database were the only database, tenants would hold the name, and every other table would point at it. Here the name already has a home, in the sign-in database. So the business database does not get a second copy, even in tenants. A rename changes one row, in one place, and every answer shows it at once.

When is a copy not a copy?

Both vendors and invoices have a terms column ("Net 30"). It looks like the transitive dependency invoice → vendor_id → terms. It is not, and only the AP expert could say so. Brightline's triage rule 3 reads: "An invoice whose terms differ from the vendor's terms is an exception." So an invoice's terms are what the invoice says, a fact about the invoice, which can differ from the vendor's agreed terms. Remove the column "to normalize it" and you remove the very fact the rule checks. This is why the data contract is co-built: whether a column is a copy or its own fact is a business question, not a database one.

What does an AI system keep on every row, on purpose?

Two things on policy_passages look like copies. tenant_id repeats the document's company, but it is part of the key, so 3NF allows it. The embedding is the one real exception to 3NF, kept on purpose. The lab keeps both, and each has a rule in the database that keeps it true. That is the difference between a careless copy and a deliberate one:

The second guard, in plain words. The database computes a fingerprint (a SHA-256 hash, Chapter 15) of the passage's text by itself, with a small helper function in the migration, ap_utf8_sha256, that hashes the text's bytes exactly as the loader does. The loader stores the fingerprint of the text it actually embedded. If anyone changes the text without embedding it again, the two fingerprints differ and the database refuses the change. In the lab, Claude Code tried exactly that, editing Cedar Ridge's "$500.00" rule to "$5,000.00" without a new embedding, and got: new row ... violates check constraint "embedding_matches_content". The text cannot change behind the embedding's back, because the database will not let it. Be precise about what this proves. It proves that the text the loader says it embedded is the text in the row now. It does not prove that the numbers really came from that text, or from the model named in embedding_model. That rests on the loader, which is trusted code, tested like any other (19.12), and on the question set, which notices when embeddings stop matching their text (19.16). The check is a guard against stale embeddings, not proof that an embedding is correct.

F5 shows how the migration writes the guard, line by line.

Why must the vector sit on the same row?

The search must keep only this company's passages and order them by distance in meaning in one step. If the embedding sat in a table of its own, the search could return fewer passages than asked for, with no error. 19.13 shows the experiment, once it has explained how a vector search works.

Which copy matters most?

Row-level security guards the one copy that matters most. tenant_id is the one fact copied onto every row, and it is the one that decides who sees the row. So the database itself enforces it, with a rule on every table that 19.8 explains word by word.

What is the rule, in one line?

3NF for AI workers: a company's name lives in one place only, tenant_id is the one fact copied onto every row, on purpose, for isolation, and any other copy made for the search, such as the embedding beside its text, needs a rule in the database that keeps it true.

At work. In Lab 19, Task 3b, Claude Code is handed a flat table someone built in a hurry, docs_flat, with the company's name, the AP contact's email and the document's title copied onto every passage. The builder lists the transitive dependencies by reading it, before asking for any change. Then Claude Code refactors it to 3NF, adds the vector index and row-level security, and proves a rename now changes one row.

The operating rule: store each fact once, where its key is. Copy a fact only when the search needs it on the same row, and then give the copy a rule in the database that keeps it true. Ask the AP expert before removing a column that looks like a copy.

Figure 19.11. A careless copy drifts. A deliberate copy has a rule in the database that keeps it true.