Normalization.

Normalization is deciding how to split your data into separate tables so that every fact, like a person’s email or a model’s size, is written down in exactly one place.

It sounds like tidying up, but it decides whether your numbers can be trusted. Wherever a fact is copied, the copies can drift apart, and then a dashboard counts one model as two, or a plot quietly splits one person’s runs in half. Experiment results, eval scores for your ranker, logs of what an agent did: anything people keep adding to for months grows these copies, and nobody notices until a number looks wrong. “Design the tables for this” is also a staple of interviews, and the reasoning here is what they’re listening for.

Why not a JSON file? showed why data belongs in a database at all. This lesson is about its shape. It leans on Indexes and B-trees to explain why putting split-up tables back together is cheap, and on Transactions for keeping a big data move all-or-nothing. The cost it leaves you with, copies that go out of date, is the whole subject of Caching and Redis.

Today

• Look at any table (a spreadsheet, a CSV, a Postgres table someone else made) and point at the exact facts that are stored more than once, and the bugs that copying invites.
• Say in one plain sentence the rule behind all of this, “if you know this column, you know that one”, and use it to explain the four classic levels of tidy table design (called normal forms: 1NF, 2NF, 3NF and BCNF) without reciting definitions.
• Choose on purpose which columns identify a row and how one table points at another, and decide between a real-world identifier like an email and a made-up number, knowing which one breaks when an email address changes.
• Take a messy CSV of your own experiment runs, find its contradictions with SQL, design clean tables with rules the database itself enforces to refuse bad data, move the data across, and rebuild the original sheet by stitching the tables back together.
• Decide when to break the rules on purpose (dashboards, analytics warehouses, caches, document databases, JSON columns), and say what you pay for it: copies that go out of date.

You’ll need basic SQL, at the level of having written a SELECT with a WHERE, a GROUP BY and a JOIN once or twice. Everything runs in SQLite, the small database that ships with macOS and Python. Plan on one evening.

The Problem

Your team keeps eval results in a shared sheet. One row per metric per run: run id, who ran it, their email, the model, its parameter count, the dataset and its version, the metric, the value. It started as twenty rows. It’s now a few thousand, exported to runs.csv every week so a notebook can plot it.

Then three small things happen in the same month.

Sam changes his email address when his name gets updated in the company directory. Someone does a find-and-replace on the sheet, but only on the rows that were visible after a filter. Now Sam has two emails, and the notebook that groups by email shows him as two people.

Priya registers a new dataset, tf-clicks v1, with 40,000 labeled pairs. She wants it in the sheet so people know it exists. But every row in the sheet is a metric of a run, and nobody has run anything on it yet. There is no row to put it in. She sends a Slack message instead, which nobody will find in three months.

Then someone cleans up failed runs. One of them was the only run ever done with ce-deberta-v3-large. The rows go, and with them the only place anyone wrote down that model is 435M parameters.

None of these is anyone’s fault. They are the table’s fault. The sheet stores each fact many times, or only as a side effect of storing some other fact, and so every edit is a chance to make it disagree with itself. You’ve probably met this at work under its official name. The fix is called normalizationnormalizationDesigning tables so that each fact is stored in exactly one place. Mostly done by splitting one wide table into several narrower ones linked by ids., and the idea is older than almost everything else you use.

The Idea

Three ways a table lies

The three stories above are the three anomalies, and they are worth naming because you’ll start seeing them everywhere.

An anomalyanomalyA way a table’s design lets it end up wrong or lets a fact get lost, even when every individual edit was reasonable. is not a bug in any query. It’s a bug in the shape of the table.

All three have one root cause, and it fits in a sentence you can carry around: the table mixes several kinds of thing into one row. A row in our sheet is simultaneously “a metric value”, “a run”, “a person”, “a model” and “a dataset”. Each of those things has its own facts, and they’ve been glued together and copied wherever the glue went.

Play with it. Click a cell in the flat sheet below and every other cell that holds the same fact lights up: those are the rows you’d have to edit together. Change one row only and watch the sheet start contradicting itself. Then switch to the normalized version and make the same edit.

Notice which cell in the flat sheet was already fine: the metric value. It lives in exactly one row, because the row is that fact (this run, this metric, this number). Everything else in the row is a passenger. Normalization is the process of giving each passenger a seat of its own.

“If you know X, you know Y”

To do that on purpose rather than by eye, you need one tool, and it’s simpler than its name. A functional dependencyfunctional dependencyA rule about your data of the form “if you know X, you know Y”: any two rows that agree on X must agree on Y. Written X → Y. is a rule of the form: if you know this, you know that.

• If you know the run_id, you know which model it used. Every row with run-101 must say the same model.
• If you know the author_email, you know the author’s name and team.
• If you know the model_name, you know its parameter count.
• If you know the run and the metric name, you know the metric value. Knowing only the run isn’t enough: run 101 has three different values.

It’s written run_id → model_name, read “run_id determines model_name”. The left side is the determinantdeterminantThe “if you know this” side of a functional dependency. The columns that pin down some other column..

Two things to notice. First, these rules are facts about the world, not about the data you happen to have. Maybe today every model in the sheet with 33M params is a MiniLM, but that doesn’t mean model_params → model_name; tomorrow someone trains a 33M e5. You decide dependencies by asking “could two rows ever legitimately disagree on this?”, not by running a query. Queries can only disprove a dependency (find two rows that disagree), never prove one.

Second, every anomaly in Figure 001 is a dependency being stored badly. author_email → author_name is true, but the table stores the pair once per metric row, so the rule is enforced by nothing but human care.

Keys, said plainly

A keykey (candidate key)A smallest set of columns that pins down a whole row: if you know these, you know everything else in it. A table can have more than one. is a dependency that reaches everything: a set of columns that, once you know them, tells you every other column in the row. In the flat sheet, run_id alone isn’t a key (run 101 has three rows), but (run_id, metric_name) is. The one you pick to be the identity of a row is the primary keyprimary keyThe key the table officially uses to identify rows. The database refuses duplicates and empty values in it., and the database will refuse two rows with the same one.

Now keep that in mind and look back at Figure 003. The middle column depends on half the key. The right column depends on something that isn’t a key at all. That’s the whole theory of normal forms, as two complaints about arrows.

The normal forms, as one rule applied four times

Each normal form is a stricter version of the same promise: every fact lives in exactly one place. Each one removes a specific kind of copy, and with it a specific anomaly. You don’t need to memorise them as definitions. You need to recognise the copy each one forbids.

First normal form (1NF): one value per cell, and rows you can tell apart. Our sheet has a tags column holding bm25-negs;hardneg. That cell is a list hiding inside one value. To find runs tagged hardneg you write tags LIKE '%hardneg%', which also matches a tag someone later calls hardneg-v2. You can’t put a constraint on “each tag is one of these eight”, can’t index it, can’t count tags without string surgery. The 1NF fix is to give the list its own table: run_tags(run_id, tag), one row per tag. The same rule forbids repeating groupsrepeating groupsColumns that are really a list spread sideways, like metric1_name, metric1_value, metric2_name, metric2_value. Adding a fifth metric means changing the table. like metric1_name, metric1_value, metric2_name, .... A value is atomicatomicTreated as one indivisible value by the database. “Atomic” is relative to how you query it: a date is atomic until you constantly need just the month. when you never need to look inside it to answer your questions.

Second normal form (2NF): every column depends on the whole key, not part of it. The key is (run_id, metric_name), but model_name depends on run_id alone. So the model is written once per metric: three times for run 101. The anomaly is a run that disagrees with itself (two of its rows say MiniLM-L12, one says L6). The fix: move the run-level facts into runs, keyed by run_id alone, and leave metrics(run_id, metric_name, value) with only the column that needs the whole key. A table whose key is a single column can’t violate 2NF, since there is no “part” of the key to depend on.

Third normal form (3NF): non-key columns depend on the key and nothing else. Now runs has run_id → model_name → model_params. The params depend on the key only through another column. That’s a transitive dependencytransitive dependencyA dependency that goes through a middle column: the key determines B, and B determines C. C then gets copied wherever B appears., and it’s why every run of MiniLM-L12 repeats “33M”. Same with author_email → author_name, author_team and (dataset_name, dataset_version) → dataset_rows. The fix: authors, models, datasets tables, and runs holds only a pointer to each. This is where the insert and delete anomalies disappear too. A dataset with no runs is a row in datasets, with no run needed. Deleting the only DeBERTa run leaves the models row alone.

There’s an old summary of 2NF and 3NF together, from Bill Kent’s 1983 guide: every non-key column must depend on the key, the whole key, and nothing but the key. (People like to add “so help me Codd”.)

Boyce-Codd normal form (BCNF): if you know X, you know Y, only when X is a key. 3NF has a loophole: it only polices non-key columns. Here’s a case it misses. Say each eval harness has one owner, and each owner maintains exactly one dataset’s harness. You record sign-offs in a table signoffs(model, dataset, owner): each model on each dataset is signed off by one owner. The dependencies are (model, dataset) → owner and owner → dataset. Both (model, dataset) and (model, owner) are keys, so every column is part of some key, and 3NF is satisfied. But owner → dataset means “Lena owns msmarco” is copied into every one of Lena’s sign-off rows, and you can’t record that Lena owns msmarco until she’s signed off a model. Same anomalies, new disguise. BCNF closes the loophole: the left side of every dependency must be a key. The fix is owners(owner, dataset) and signoffs(model, owner).

BCNF has a price worth knowing about. After that split, the rule “one owner per model per dataset” is no longer something a single UNIQUE constraint can enforce, because model and dataset are now in different tables. You’d need a trigger or application code. This is the one place where the “more normal” design is not strictly better, and textbooks call it losing dependency preservationdependency preservationWhether every rule about your data can still be checked inside a single table after you split it. BCNF sometimes gives this up; 3NF never has to.. In practice, most people aim for 3NF and check that it’s also BCNF, which it usually is.

There are higher normal forms (4NF, 5NF) for rarer cases. The one worth one sentence: if you stored a run’s tags and the GPUs it ran on in one table (run_id, tag, gpu), you’d need a row for every tag-GPU combination, though the two lists have nothing to do with each other. 4NF says: two independent lists go in two tables. Kent’s guide covers both in a few pages.

Foreign keys: the arrows, enforced

Once runs only store author_id, something has to guarantee that the id points at a real author. That’s a foreign keyforeign keyA column whose values must exist as a key in another table. The database refuses an insert that points at nothing, and refuses to delete a row others still point at (unless told what to do instead).: runs.author_id REFERENCES authors(author_id). The database now refuses a run whose author doesn’t exist, and refuses to delete an author who still has runs. You can tell it what to do instead: ON DELETE CASCADE means “delete their runs too” (right for metrics, which mean nothing without their run), ON DELETE RESTRICT means “refuse” (right for authors). This guarantee is called referential integrityreferential integrityThe promise that every reference points at something that exists. No orphans..

This is where “good schemas make bugs impossible instead of just unlikely” becomes literal. A constraintconstraintA rule the database checks on every write and refuses to break: NOT NULL, UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY. is a dependency you’ve told the database about. UNIQUE(email) is the database enforcing email → author. PRIMARY KEY (run_id, name) on metrics is the database enforcing “one value per metric per run”. CHECK (params_m > 0) stops someone pasting “33M” into a number column. Code review catches some bad writes. A constraint catches all of them, forever, including the ones from the notebook someone runs at 2 a.m.

Natural or surrogate keys

Which column should identify an author? The obvious choice is the email: it’s unique, everyone has one. A key that is a real-world fact like that is a natural keynatural keyA key made of real data that means something outside the database: an email, an ISBN, a dataset name plus version..

The trouble is in the first story. Emails change. If authors is keyed by email and runs stores the email as a foreign key, Sam’s email change becomes an update of every run that points at him: the update anomaly is back, now with a constraint that blocks the change halfway (you’ll hit exactly this in Break It). The usual fix is a surrogate keysurrogate keyA meaningless id the database makes up (1, 2, 3 or a UUID) purely to identify a row. It never needs to change, because it doesn’t mean anything.: author_id INTEGER PRIMARY KEY. It means nothing, so nothing in the world can make it change. You keep the email as UNIQUE, so it still can’t be duplicated, but nothing points at it.

The rule of thumb: use a surrogate key for things whose identifying facts can change (people, models that get renamed, companies). A natural key is fine when it’s truly permanent, or when the row is defined by it: metrics(run_id, name) is naturally identified by “this run, this metric”, and inventing a metric_row_id adds nothing. datasets gets a surrogate id but also UNIQUE(name, version), so the natural identity is still enforced.

Joins are the price

The normalized schema has no row that looks like the original sheet. To get it back you join: metrics to runs, runs to authors, models and datasets. A joinjoinCombining rows from two tables where a column matches, like metrics.run_id = runs.run_id. It’s how normalized data gets reassembled into the view you want to read. is the price you pay for storing each fact once. You trade a little work at read time for correctness at write time.

How big is that price? Smaller than people fear, as long as the columns you join on have an indexindexA sorted lookup structure (usually a B-tree) on one or more columns, so the database can find matching rows without scanning the whole table. (primary keys always do; for foreign keys you usually add one yourself). Looking up one run with all its author, model and dataset details is five index lookups, each a few microseconds. Scanning everything for a dashboard costs about the same either way, because the time goes to reading 600,000 metric rows, not to matching them up with six authors. You’ll measure both. The indexes lesson is where the “why” lives.

Where joins do hurt: many-table joins over billions of rows in an analytics query, joins across machines (the data for each side lives on different servers and has to cross the network), and the read path of a page that must render in 5 ms. That’s when you start thinking about breaking the rules.

When to denormalize on purpose

denormalizationdenormalizationDeliberately storing a fact in more than one place (or precomputing a join) to make reads faster or simpler, and accepting the work of keeping copies in sync. is not the absence of design. It’s normalizing first, then copying on purpose, with a plan for keeping the copies right. Some places where that’s the correct call:

Read-heavy dashboards. Your eval dashboard runs the same “best nDCG per model per dataset” query every time anyone opens it, and new runs land a few times an hour. Computing it once and storing the result is a materialized viewmaterialized viewA stored copy of a query’s result, kept as a real table. Reading it is instant, but it’s only as fresh as the last time it was refreshed.. Postgres has them built in (CREATE MATERIALIZED VIEW, then REFRESH MATERIALIZED VIEW when you want it updated). SQLite doesn’t, so you’ll build one by hand, which is more instructive anyway.

Warehouses and star schemas. Analytics databases (BigQuery, Snowflake, Redshift, DuckDB) are built for scanning huge tables, and they usually store data column by column, so a wide table with repeated strings compresses well and costs little. The classic layout is a star schemastar schemaA warehouse layout with one big “fact” table of events or measurements in the middle, and “dimension” tables of descriptive attributes around it, which are deliberately left denormalized.: one central table of measurements (each eval result), surrounded by wide descriptive tables (models, datasets, people, dates). The dimension tables are deliberately unnormalized: dim_model might carry family, size, base checkpoint and owning team all in one row, even though team really depends on the owner. Nobody updates these in place; they’re rebuilt by a pipeline from the normalized source.

Caches. A Redis key holding a rendered leaderboard is denormalized data with an expiry date. Same trade, same staleness problem; the caching lesson is about managing it.

Document stores. In MongoDB or a JSON blob in S3, you usually embedembedStore related data inside the parent document (a run’s metrics as a list inside the run) instead of in a separate table. One read gets everything. things that are always read together and change together: a run’s metrics belong inside the run document. Things that are shared and change independently (the author’s email) are still better referenced by id, or you’ve rebuilt the flat sheet in JSON. Martin Kleppmann’s chapter on data models is the best treatment of where that line sits.

History that should not change. This one is subtle and important. An invoice stores the price at the time of sale, even though the product table has a price, because the invoice is supposed to remember the past. If your runs table stores “which version of the dataset was used”, that’s not a copy of a changing fact; it’s a fact about the run. Copying is only an anomaly when the copies are supposed to agree. Append-only logs of things that happened (eval results, events, traces) rarely have update anomalies, because nobody updates them.

JSON columns, the pragmatic middle

Hyperparameters are awkward. A LoRA run has r and alpha; a distillation run has a temperature and a teacher; next month’s runs will have something you haven’t thought of. A column per hyperparameter means a schema change per experiment. The textbook-normal alternative is a table params(run_id, key, value), one row per hyperparameter. It’s called EAVEAV (entity-attribute-value)Storing data as rows of (thing, attribute name, value) instead of columns. Flexible, but every value becomes text and every question becomes a self-join., and MLflow’s tracking database does exactly this. It works, but every value is a string, and “runs where lr = 3e-5 and r = 16” becomes a join of the table with itself.

Modern databases offer a middle: a JSON column. runs.config holds {"lr": 3e-5, "lora": {"r": 16}}, and the database can reach inside it (config->>'$.lora.r' in SQLite, config->'lora'->>'r' in Postgres, where the jsonb type can also be indexed). The rule for when it’s fine:

• Put things in JSON that you mostly read back whole, that vary between rows, and that nothing else depends on (no if you know X you know Y hiding inside).
• Keep things as real columns if you join on them, enforce rules on them, or filter on them constantly.
• When a JSON field turns out to be hot (everyone filters by lora.r), promote it: a generated columngenerated columnA column the database computes from other columns in the same row, like pulling a field out of JSON. It can be indexed like a normal column. pulls it out, and you index that.

Normalization tells you where facts should live. JSON columns are an honest way to say “this part I haven’t designed yet, and I’m choosing not to”.

Staleness, the bill for every copy

Every denormalized copy has the same failure: it can be out of date. A materialized view refreshed every 15 minutes is, on average, several minutes behind, and in the worst case almost a full interval plus however long the refresh takes.

Your options, from simplest to most work: refresh on a timer and print “updated 4 min ago” on the dashboard (honest, and usually enough); refresh right after each batch of writes; or keep the copy updated incrementally with triggerstriggersSmall pieces of code the database runs automatically when a row is inserted, updated or deleted. Can keep a summary table in sync with every write., so each insert also bumps the summary. Incremental is fresher and fiddlier: every write gets slower, and a bug in the trigger is a silent wrong number. The question to ask isn’t “how fresh can I make it” but “how stale is acceptable for the person reading it”, which is a product question, not a database one.

Build It

Everything goes in ~/workbench/normalization/. You need only Python 3 and the sqlite3 command, both already on your Mac and on the Spark. A Postgres variant is at the end, optional.

# make_runs.py: write runs.csv, a deliberately messy flat log of experiment runs.
# One row per (run, metric). Usage: python3 make_runs.py [n_runs] [out.csv]
import csv, random, sys
from datetime import datetime, timedelta

N_RUNS = int(sys.argv[1]) if len(sys.argv) > 1 else 600
OUT = sys.argv[2] if len(sys.argv) > 2 else "runs.csv"
rng = random.Random(7)

AUTHORS = [  # name, email, team, weight
    ("Priya Raman",  "priya@example.com",  "ranking", 14),
    ("Sam Okafor",   "sam@example.com",    "ranking", 8),
    ("Lena Fischer", "lena@example.com",   "search",  5),
    ("Diego Alvarez","diego@example.com",  "search",  4),
    ("Mei Tanaka",   "mei@example.com",    "infra",   3),
    ("Alex Kim",     "alex@example.com", "ranking", 6),
]
MODELS = [  # name, family, params, weight
    ("ce-minilm-l6",       "cross-encoder", "22M",  10),
    ("ce-minilm-l12",      "cross-encoder", "33M",  12),
    ("ce-bge-reranker-base","cross-encoder","278M",  6),
    ("bi-e5-small",        "bi-encoder",    "33M",   5),
    ("bi-e5-base",         "bi-encoder",    "110M",  4),
]
RARE_MODEL = ("ce-deberta-v3-large", "cross-encoder", "435M")  # exactly one run
DATASETS = [  # name, version, rows, weight
    ("msmarco-dev",  "v1", 6980, 8),
    ("msmarco-dev",  "v2", 6980, 6),
    ("tf-queries",   "2026-05", 12000, 5),
    ("tf-queries",   "2026-08", 15400, 6),
    ("beir-nfcorpus","v1", 323, 2),
]
METRICS = ["ndcg@10", "mrr@10", "recall@100", "p50_ms"]
TAGS = ["hardneg", "bm25-negs", "fp16", "bf16", "distill", "lora", "baseline", "sweep"]

def pick(items):
    return rng.choices(items, weights=[x[-1] for x in items])[0]

rows = []
t0 = datetime(2026, 3, 1, 9, 0)
for i in range(1, N_RUNS + 1):
    started = t0 + timedelta(minutes=int(i * 330 * 600 / N_RUNS) + rng.randint(0, 50))
    a_name, a_email, a_team, _ = pick(AUTHORS)
    m_name, m_family, m_params, _ = pick(MODELS)
    if i == N_RUNS // 2:
        m_name, m_family, m_params = RARE_MODEL
    d_name, d_ver, d_rows, _ = pick(DATASETS)

    # --- the drift a real shared sheet accumulates ---
    if a_name == "Sam Okafor" and started > datetime(2026, 6, 1) and rng.random() < 0.85:
        a_email = "sam.okafor@example.com"          # half-finished email change
    if m_name == "ce-minilm-l12" and rng.random() < 0.06:
        m_params = "33.4M"                           # someone "corrected" it in a few rows
    if m_name == "ce-minilm-l6" and rng.random() < 0.03:
        m_name = "CE-MiniLM-L6"                      # same model, different spelling
    if d_name == "msmarco-dev" and d_ver == "v2" and rng.random() < 0.05:
        d_rows = 6890                                # typo copied forward
    if a_name == "Lena Fischer" and rng.random() < 0.04:
        a_team = "ranking"                           # moved teams? or a mistake?

    lr = rng.choice(["1e-5", "2e-5", "3e-5", "5e-5"])
    bs = rng.choice([16, 32, 64])
    tags = ";".join(sorted(rng.sample(TAGS, rng.randint(0, 3))))
    base = {"ce": 0.36, "bi": 0.31}[m_name.lower()[:2]] + (0.03 if "hardneg" in tags else 0)
    n_metrics = rng.randint(2, 4)
    for metric in METRICS[:n_metrics]:
        if metric == "p50_ms":
            val = round(rng.uniform(4, 40), 1)
        elif metric == "recall@100":
            val = round(min(0.99, base + 0.5 + rng.gauss(0, 0.02)), 4)
        else:
            val = round(base + rng.gauss(0, 0.02), 4)
        rows.append({
            "run_id": f"run-{i:05d}", "started_at": started.isoformat(timespec="minutes"),
            "author_name": a_name, "author_email": a_email, "author_team": a_team,
            "model_name": m_name, "model_family": m_family, "model_params": m_params,
            "dataset_name": d_name, "dataset_version": d_ver, "dataset_rows": d_rows,
            "lr": lr, "batch_size": bs, "tags": tags,
            "metric_name": metric, "metric_value": val,
        })

with open(OUT, "w", newline="") as f:
    w = csv.DictWriter(f, fieldnames=list(rows[0]))
    w.writeheader()
    w.writerows(rows)
print(f"wrote {len(rows)} rows for {N_RUNS} runs to {OUT}")

python3 make_runs.py                 # wrote 1763 rows for 600 runs to runs.csv
sqlite3 flat.db ".import --csv runs.csv flat"
sqlite3 flat.db ".mode box" "SELECT * FROM flat LIMIT 4"

-- If you know the author's name, you should know their email.
SELECT author_name,
       COUNT(DISTINCT author_email)       AS n,
       group_concat(DISTINCT author_email) AS emails
FROM flat
GROUP BY author_name
HAVING n > 1;

PRAGMA foreign_keys = ON;   -- OFF by default in SQLite, per connection!

CREATE TABLE example (
  id      INTEGER PRIMARY KEY,                 -- a surrogate key
  name    TEXT NOT NULL UNIQUE COLLATE NOCASE, -- unique, ignoring case
  size_m  REAL NOT NULL CHECK (size_m > 0),
  parent  INTEGER NOT NULL REFERENCES parents(id) ON DELETE RESTRICT,
  UNIQUE (name, size_m)                        -- multi-column uniqueness
) STRICT;                                      -- refuse wrong types (SQLite 3.37+)

# migrate.py
import csv, sqlite3

def load_rows(path: str) -> list[dict]: ...

def canonical_author(rows_for_author: list[dict]) -> tuple[str, str]:
    """(email, team) for one person. Which email wins: the newest? the most common?"""
    ...

def canonical_model(rows_for_model: list[dict]) -> tuple[str, str, float]:
    """(name, family, params_m). Merge case variants. Pick one param count."""
    ...

def main(csv_path: str, db_path: str) -> None:
    con = sqlite3.connect(db_path)
    con.execute("PRAGMA foreign_keys = ON")
    con.executescript(open("schema.sql").read())
    # 1. parents first: authors, models, datasets (remember the id each got)
    # 2. runs: one per distinct run_id, pointing at the ids from step 1
    # 3. run_tags: split the tags cell
    # 4. metrics: one per CSV row
    # all inside one transaction: `with con:` commits on success, rolls back on error
    ...

def refresh(con) -> float:
    """Rebuild dash_mv from the base tables in one transaction. Return seconds taken."""
    ...

SELECT run_id FROM runs WHERE config->>'$.lora.r' = 16;
EXPLAIN QUERY PLAN SELECT run_id FROM runs WHERE config->>'$.lora.r' = 16;   -- a full scan

ALTER TABLE runs ADD COLUMN lora_r INTEGER GENERATED ALWAYS AS (config->>'$.lora.r') VIRTUAL;
CREATE INDEX runs_lora_r ON runs(lora_r);
EXPLAIN QUERY PLAN SELECT run_id FROM runs WHERE lora_r = 16;                -- uses the index

CREATE MATERIALIZED VIEW dash_mv AS SELECT ...;
CREATE UNIQUE INDEX ON dash_mv (model_id, dataset_id);
REFRESH MATERIALIZED VIEW CONCURRENTLY dash_mv;   -- readers keep seeing the old copy meanwhile

Measure

Run each measurement at 600 runs and again at 200,000 (python3 make_runs.py 200000 big.csv, then the same load and migration). Time queries with time.perf_counter() in Python, best of 5 runs.

My numbers, Apple Silicon Mac, Python 3.14 with SQLite 3.53, at 200,000 runs (600,037 rows). Yours will differ; the shape shouldn’t.

What the numbers should tell you:

The update is the headline. Writes in the flat table scale with how often a fact is repeated. Writes in the normalized one don’t. At 600 runs both are fast and the difference doesn’t matter for speed; it matters for correctness. At 200k runs, the flat email change rewrites a third of the table.

The join was nearly free. The live dashboard took about the same time either way. Both versions have to read every ndcg@10 row; matching each to one of six models is cheap next to that. If you expected the join to be much slower, that’s the useful surprise. Joins get expensive when both sides are huge and unindexed, not when a big table points at small ones.

The flat dashboard is wrong. 31 groups instead of 26: CE-MiniLM-L6 and ce-minilm-l6 are counted as different models, so each of its five datasets appears twice, with the runs split between them. Nobody would notice in a chart with 31 bars. The normalized version can’t produce this, because COLLATE NOCASE UNIQUE refused the duplicate during migration.

Size: smaller, but not dramatically. The repeated strings in the flat table are short, and the normalized tables add indexes and repeat run_id in metrics and run_tags. At 600 runs the two files are about the same size (around 300 KB each). Normalization is about correctness first. The savings get large when the repeated things are big (long descriptions, configs) or many.

The one-run lookup is mostly an index story. 33 ms vs 0.01 ms isn’t normalization; it’s that the flat table has no index on run_id. Add one (CREATE INDEX ON flat(run_id)) and rerun: the flat lookup should drop to a few microseconds too. Keep that in mind whenever someone shows you a benchmark: check what they’re actually comparing.

The materialized view is thousands of times faster to read, and that number is useless on its own. Pair it with how often you refresh and what that costs. At 154 ms per refresh, refreshing every minute costs 0.25% of one core. Refreshing on every write would make each insert 154 ms slower, which is when you’d reach for triggers or incremental updates.

Break It

1. Forget PRAGMA foreign_keys = ON. Open a fresh sqlite3 norm.db and run INSERT INTO runs VALUES ('run-x', '2026-01-01', 999, 1, 1, 1e-5, 32); (adjust to your columns). It succeeds: author 999 doesn’t exist, and SQLite didn’t check. Foreign key enforcement is off by default in SQLite, for backwards compatibility, and it’s set per connection, so every script and every notebook has to turn it on. Now turn it on, retry, and watch it fail. Then run PRAGMA foreign_key_check; to find any orphans that slipped in while it was off. (Postgres and MySQL’s InnoDB enforce foreign keys by default, so this is a SQLite trap specifically, but “the rule exists and isn’t checked” happens everywhere: a session that switched checks off for a bulk load, a rule enforced only in one service’s code.)

2. Use the email as the key. Make a copy of your schema where authors is keyed by email and runs.author_email REFERENCES authors(email). Migrate, then change Sam’s email in authors. With foreign keys on, it fails: FOREIGN KEY constraint failed, because 132 runs point at the old value. You can add ON UPDATE CASCADE and the database will rewrite all 132 for you, which works, but notice what you’ve built: the update anomaly, automated. That’s the case for surrogate keys in one experiment.

3. Over-normalize into EAV. Move every run attribute into run_attrs(run_id, key, value TEXT): model, dataset, lr, batch size, everything. Now write “best ndcg@10 per model, for runs with lr = 3e-5 and batch size 32”. Count the self-joins. Check what '3e-5' = 3e-5 gives you when everything is text. EAV is maximally flexible and minimally checkable: no types, no foreign keys, no NOT NULL for the attributes that matter. Normalization says one fact in one place, not “as many tables as possible”.

4. Let the dashboard drift. Insert 1,000 new runs (generate them with a different seed and migrate only the new ones) and don’t refresh. Compare dash_mv to the live query: the counts are off by 1,000 runs’ worth and the “best” column may be wrong. Now add a refreshed_at check that raises if the copy is older than 10 minutes. This is the whole job of a cache: a copy, plus a rule for when the copy is too old to trust.

Explain It Back

Answer out loud, in plain words, as if in an interview. Then check.

Go Deeper

• E. F. Codd, “A Relational Model of Data for Large Shared Data Banks”, Communications of the ACM 13(6), 1970. Short and surprisingly readable. Section 1 is the motivation (programs breaking when storage changes), and normal form shows up in section 1.4.
• E. F. Codd, “Further Normalization of the Data Base Relational Model” (IBM Research Report RJ909, 1971; published 1972 in R. Rustin, ed., Data Base Systems). Where 2NF, 3NF and the insertion, update and deletion anomalies are first named.
• William Kent, “A Simple Guide to Five Normal Forms in Relational Database Theory”, Communications of the ACM 26(2), 1983. The best short explanation of all five normal forms ever written, with examples instead of algebra. Read this before any textbook chapter.
• Martin Kleppmann, Designing Data-Intensive Applications, chapter 2 (“Data Models and Query Languages”) on normalization versus documents and when to embed, and chapter 3 for star schemas and column-oriented storage.
• Ralph Kimball and Margy Ross, The Data Warehouse Toolkit. The standard reference for star schemas and dimensional modeling, and the clearest argument for why warehouses denormalize.
• Silberschatz, Korth and Sudarshan, Database System Concepts, the chapter on relational database design. The formal version: closures of functional dependencies, the BCNF decomposition algorithm, and why BCNF can lose dependency preservation.
• SQLite docs: Foreign Key Support, STRICT Tables, JSON functions, and PostgreSQL docs: Materialized Views. The exact behavior of everything you used in the build.
• MLflow’s tracking schema, in the MLflow source: models.py under mlflow/store/tracking/ dbmodels/ (check the current layout). A real experiment-tracking schema: normalized runs and experiments, EAV-style params and tags, and a separate latest_metrics table that is denormalization on purpose, kept so the UI doesn’t have to scan every metric step.

Connections

This lesson is the “shape” half of databases. Indexes and B-trees is the “speed” half: why the joins here were cheap, and why the flat lookup without an index wasn’t. Transactions covers what kept your migration and your refresh() all-or-nothing, and what happens when two people edit the same row at once. Why not a JSON file? is the step before this one: why the sheet should be a database at all.

The staleness problem from the last section is the whole subject of Caching and Redis: a cache is a denormalized copy with an expiry date, and every lesson here about “how old is this copy, and who checks” applies there with more traffic.