Databases I: Relational Thinking
Your dict-of-dicts wants to be tables — schemas, keys, and normalization as anomaly prevention, not dogma.
🎯 By the end you'll understand
Why the relational model exists (and what problem it actually solves), how to identify entities, relationships, and cardinality in a real system, what primary and foreign keys do mechanically, what normalization is really about — duplication control — and how to model TraceLab's runs, metrics, artifacts, and agents as a proper schema. You'll also be able to identify update, insert, and delete anomalies in a design before they bite you in production.
KEY INSIGHTYour CSV is already a broken database
The relational model didn't invent constraints — it just moved them from your application code, where they're optional and forgettable, into the database engine, where they run forever.
TraceLab v0's runs.csv is a flat file where every row is an experiment: the timestamp, the run name, and then a variable-length list of values. It works until it doesn't. You rename a metric halfway through a project and now half the rows say val_loss and half say validation_loss — they're the same thing, but the file has no way to enforce that. A robot crashes mid-run, writing a partial row, and find_run returns corrupted data silently. Two runs log simultaneously and their bytes interleave. You want to attach a model checkpoint artifact to a run, so you add another column — except some runs don't have checkpoints, and now every old row has a dangling empty field.
Every problem listed above is a data integrity problem that the application code is failing to enforce. The relational model — tables, keys, constraints — is the engineering answer to that failure. It doesn't make your data problems go away; it moves the invariants out of your application code (which runs once per query, if someone remembers to call it) and into the database engine (which runs on every write, forever). That's why Codd's 1970 paper still matters: not the math, but the insight that data structure is a form of running code.
CORE CONCEPTEntities, relationships, and cardinality
Before you write a single CREATE TABLE, you need to know what things your system tracks and how they relate to each other. This is data modeling, and it is the skill most developers skip — then spend six months paying for.
An entity is a distinct thing your system cares about — something with its own identity that can exist independently. For TraceLab, the obvious entities are:
- Run — a single experiment execution (a robot episode, a training job, a benchmark)
- Metric — a named numerical measurement taken during a run (loss, reward, latency)
- Artifact — a file produced by a run (checkpoint, video, plot, config YAML)
- Agent — the agent/model configuration that was running (architecture, hyperparams)
A relationship describes how entities connect. But vague "connects to" language isn't enough — you need cardinality: the numeric ratio. There are three:
| Cardinality | What it means | TraceLab example | How it maps to tables |
|---|---|---|---|
| One-to-one (1:1) | Each A has at most one B and vice versa | A run has one configuration snapshot | Foreign key in either table, or merged |
| One-to-many (1:N) | One A can have many B, but each B belongs to one A | One run has many metric readings | Foreign key on the "many" side (metrics.run_id) |
| Many-to-many (M:N) | A can have many B, and B can have many A | A run can use many tags; a tag can label many runs | Junction/join table with two foreign keys |
Getting cardinality right is not academic. If you model a 1:N relationship as a comma-separated list in a single column (storing "loss,reward,latency" instead of three rows), you have baked a query-time parsing problem into every aggregation you will ever write. The CSV failure from v0 was exactly this: a variable-length sequence stuffed into a single row, with the database unable to know where one field ends and the next begins.
If you find yourself adding a column that only applies to some rows (an artifact column on the runs table, which only makes sense for runs that produced one), that's usually a sign the entity belongs in a separate table. NULL proliferation is often cardinality confusion in disguise.
CORE CONCEPTKeys and constraints: tests that run forever
Once you have tables, you need a way to uniquely identify each row and a way to describe how rows in different tables point to each other. This is the job of keys.
Primary key (PK): the row's identity
Every table needs a column — or combination of columns — that uniquely identifies each row. In Postgres this is declared PRIMARY KEY, which automatically enforces uniqueness (no two runs can share an id) and NOT NULL (every row must have one). The most common form is a surrogate key — a synthetic integer or UUID the database generates, with no business meaning. id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY is the modern Postgres idiom. Use UUIDs (gen_random_uuid()) when rows will be created on multiple machines and merged.
Foreign key (FK): the relationship enforcer
A foreign key says "this column must contain a value that exists as a primary key in that other table." metric.run_id REFERENCES run(id) means you cannot insert a metric for a run that doesn't exist, and you cannot delete a run that has metrics unless you decide what happens to the orphans first (ON DELETE CASCADE or ON DELETE RESTRICT). This is not just documentation — it's a constraint the database engine checks on every write. Before foreign keys, that check lived in application code, where someone always forgot to call it.
Unique constraint: business rules as guarantees
UNIQUE(run_id, metric_name, step) on the metrics table means the same step of the same metric in the same run can only be recorded once. If your ingest code runs twice due to a retry (and it will), the second insert fails instead of silently doubling your data. This is the idempotency guarantee that transactions alone can't give you. Postgres's INSERT ... ON CONFLICT DO NOTHING pairs perfectly with a unique constraint for exactly-once ingest.
CHECK constraint: arbitrary invariants
CHECK(value IS NOT NULL AND step >= 0) on the metrics table means the database will reject any attempt to record a negative step or a null value — regardless of which application, which microservice, or which manual SQL session writes the row. Constraints are not comments; they are enforced code that runs in the database engine on every transaction.
Natural keys seem appealing — using a run's human name as its primary key. The problem: natural keys change. A researcher renames a run ("exp042-final" becomes "pilot-v2"), and now every foreign key referencing that run is orphaned. Surrogate keys (integers, UUIDs) are immutable identifiers that only the database assigns. The human-readable name lives in a separate column with its own unique constraint — enforced, but decoupled from identity.
CORE CONCEPTNormalization: duplication control, not religion
Normal forms are often taught as rules to memorize. The underlying principle is simpler: every fact should be stored exactly once. When the same fact appears in multiple rows, any operation that changes that fact must update multiple rows — and if one update misses, the database now contains two conflicting versions of the same truth. That inconsistency is called an anomaly.
There are three anomalies that emerge from denormalized data, and each has a name:
| Anomaly | What happens | TraceLab example |
|---|---|---|
| Update anomaly | Changing a fact requires updating multiple rows; miss one and the DB has conflicting truths | Agent architecture "ResNet18" stored on every run row → rename it in 3 rows but not 47 → half the history now lies |
| Insert anomaly | You can't record a fact without recording an unrelated one | Can't register an agent config until it has run at least one experiment — the config has nowhere to live |
| Delete anomaly | Deleting one entity accidentally destroys unrelated facts | Delete the only run that used agent config "PPO-v7" — and the config itself is gone forever |
Normal forms are just systematic ways to eliminate these anomalies. In practice, Third Normal Form (3NF) is the right target for most OLTP schemas:
- 1NF: Each column holds a single atomic value. No comma-separated lists, no arrays of metrics in one cell — this is the main thing TraceLab v0's CSV violates.
- 2NF: Every non-key column depends on the whole primary key (relevant when the PK is composite). A metric's value must depend on the run ID and the metric name and the step — not on just one of those.
- 3NF: No non-key column depends on another non-key column. The agent's architecture type shouldn't live on the run row — that makes it a transitive dependency through the agent foreign key. It belongs in the agent table.
3NF is not a law. Reporting and analytical queries (Stage 5's domain) often join many tables and struggle with performance. Denormalized schemas — wide tables, materialized views, columnar stores — are standard in OLAP systems. The key word is deliberately: denormalize after you've profiled a normalized schema and found a real bottleneck, not preemptively to avoid learning joins. Postgres handles enormous normalized schemas gracefully with the right indexes; you almost never need to denormalize a transactional database.
SEE ITBuild the schema — watch anomalies fire
Below is TraceLab v0's CSV blob, broken into its constituent fields. Drag each field into a table by clicking it — or use the preset buttons to jump to common (mis)designs. Once you've placed some fields, click Run anomaly scan to fire three realistic scenarios at your schema and see exactly which anomalies it permits.
The goal: place fields so that the scan returns no anomalies. A field with a gold border is a primary key; blue border is a foreign key.
The "normalized" preset shows why it passes all three scenarios: each fact lives in exactly one table, no fact can be deleted without intent, and the database can record an agent config before any run uses it. The "flat" preset fails all three — and that is the TraceLab v0 CSV in database form.
IN PRACTICEWhy Postgres is the default answer
Every few years, someone proclaims SQL dead. Every few years, it isn't. Postgres specifically has become the default database for new server-side systems not through inertia but through earned breadth: full ACID transactions, JSON columns with indexing when you genuinely need semi-structured data, excellent full-text search, PostGIS for geospatial queries (relevant if you track robot positions), logical replication, and an extension ecosystem (pgvector for embeddings, TimescaleDB for time-series) that makes it competitive with specialized databases in many domains.
The practical starting schema for TraceLab v2, in real Postgres DDL:
-- Agents: the model/algorithm configuration that was running CREATE TABLE agent ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name TEXT NOT NULL UNIQUE, arch TEXT NOT NULL, config JSONB, -- hyperparams, arbitrary structure created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); -- Runs: a single experiment execution CREATE TABLE run ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, agent_id BIGINT NOT NULL REFERENCES agent(id) ON DELETE RESTRICT, name TEXT NOT NULL, status TEXT NOT NULL DEFAULT 'running' CHECK(status IN ('running','done','failed')), started_at TIMESTAMPTZ NOT NULL DEFAULT now(), ended_at TIMESTAMPTZ -- NULL until run completes ); -- Metrics: time-series measurements during a run CREATE TABLE metric ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, run_id BIGINT NOT NULL REFERENCES run(id) ON DELETE CASCADE, name TEXT NOT NULL, step INT NOT NULL CHECK(step >= 0), value DOUBLE PRECISION NOT NULL, logged_at TIMESTAMPTZ NOT NULL DEFAULT now(), UNIQUE(run_id, name, step) -- idempotent re-ingest ); -- Artifacts: files produced by a run CREATE TABLE artifact ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, run_id BIGINT NOT NULL REFERENCES run(id) ON DELETE CASCADE, kind TEXT NOT NULL CHECK(kind IN ('checkpoint','plot','config','video','other')), path TEXT NOT NULL, size_bytes BIGINT, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); -- Tags: many-to-many between runs and string labels CREATE TABLE tag ( id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, label TEXT NOT NULL UNIQUE ); CREATE TABLE run_tag ( run_id BIGINT NOT NULL REFERENCES run(id) ON DELETE CASCADE, tag_id BIGINT NOT NULL REFERENCES tag(id) ON DELETE CASCADE, PRIMARY KEY(run_id, tag_id) );
Notice the deliberate choices: ON DELETE RESTRICT on the agent FK (you must explicitly reassign runs before deleting an agent — a guard against accidental history loss), ON DELETE CASCADE on metrics and artifacts (when a run is deleted, its children are too — a run without metrics is meaningless data anyway). The UNIQUE(run_id, name, step) on metrics turns retries into no-ops. The CHECK on status is a poor person's enum — when you care about performance at scale, CREATE TYPE run_status AS ENUM is cleaner.
The config JSONB column on agent stores hyperparameter dicts without requiring a fixed schema upfront. Postgres indexes JSONB with GIN indexes and lets you query inside it: WHERE config ->> 'lr' = '0.001'. This is not a reason to avoid a proper schema for things you query repeatedly — but for genuinely variable metadata (one agent has dropout, another has attention_heads), JSONB is the right tool.
TRACELABFrom CSV blob to relational schema
TraceLab v0 stores a row per run as a flat CSV: timestamp,run_name,metric1,metric2,…. The v0 debt table from Lesson 1.9 lists "the CSV schema lives in nobody's head" as a debt to be repaid in Stage 4 — but the data model is the foundation, and it belongs here in Stage 3.
This lesson's milestone for TraceLab v2 is the schema above. Here is how the migration maps v0's flat structure onto the normalized design:
| v0 CSV column | v2 table | What changed and why |
|---|---|---|
timestamp | run.started_at | Typed as TIMESTAMPTZ — timezone-aware, not a string. Sortable, comparable, range-queryable without parsing. |
run_name | run.name + run.id | Human name decoupled from identity. The surrogate ID never changes even if you rename the run. |
metric1, metric2, … | metric rows | Variable-length columns become rows. Each metric is independently queryable, aggregatable, and can have its own timestamp and step counter. |
| (implicit) agent config | agent table | Agent config was repeated on every row of a multi-run experiment. Now it lives once with an FK reference — no update anomaly possible. |
| (missing) artifacts | artifact table | v0 had no place for checkpoint paths or plots. The FK relationship means artifacts are tightly linked to their run and cleaned up together. |
| (missing) status | run.status | A crashed v0 run was indistinguishable from a completed one — the file just had a partial row. Now there is an explicit state machine with a CHECK constraint. |
The Python side of TraceLab v2 uses psycopg (the modern psycopg3) as the database driver. The core log-a-metric call is now three lines:
def log_metric(conn, run_id: int, name: str, step: int, value: float) -> None: conn.execute( """INSERT INTO metric (run_id, name, step, value) VALUES (%s, %s, %s, %s) ON CONFLICT (run_id, name, step) DO NOTHING""", (run_id, name, step, value) )
The ON CONFLICT DO NOTHING clause is idempotent: if a worker retries after a network blip, the duplicate metric just vanishes. No duplicate data, no exception to catch. The unique constraint is doing real work here, not just documenting an assumption.
Lesson 1.1's debt table listed "the CSV schema lives in nobody's head — the great migration" as Stage 4 material. The data model is the Stage 3 foundation for that: we now have a schema with real types, real constraints, and real relationships. The query layer (indexes, joins, the planner) comes in Lesson 3.5, and the transaction isolation that protects concurrent writers comes in Lesson 3.6.
WEAKNESSWhere relational thinking bites engineers
Schema migrations are among the most expensive operations in a production system. Adding a column to a 500-million-row table requires a lock or a careful online migration strategy. Renaming a column means updating every query in every service. The data model is the decision you live with longest — longer than your choice of language, longer than your choice of framework, longer than your choice of team. A week of careful modeling before writing a line of application code routinely saves months of migration pain. The right question is not "what do I need now?" but "what will I regret in two years?"
UUIDs are globally unique across distributed systems. They're also 128 bits vs 64 for a bigint, less cache-friendly in B-tree indexes, and meaningless in logs. If your TraceLab instance runs on one machine and rows are only created there, a BIGINT GENERATED ALWAYS AS IDENTITY is faster and more debuggable. Reserve UUIDs for systems that genuinely need globally unique identifiers generated without coordination — distributed ingest pipelines, multi-region primary keys. The v2 schema above could use either; the right answer depends on deployment topology, not aesthetic preference.
3NF is the right default, but it is a default, not a mandate. Reporting queries that join six tables to compute a single metric over 10 million rows may justify a denormalized summary table. Audit logs are often deliberately append-only flat tables — joins would defeat the point. Time-series databases (InfluxDB, TimescaleDB) optimize for append-heavy narrow inserts in ways that violate 3NF by design. The mistake is not denormalizing — it's denormalizing without understanding the anomalies you're accepting, or doing it to avoid learning SQL. Know what you're trading and why.
KEY INSIGHTHow this connects
- Data Structures (1.7) — the in-memory dict-of-dicts that TraceLab used before this lesson. Every relational operation has an in-memory analogue: a foreign key is a dict lookup; a unique constraint is a set membership check; a join is a nested loop or hash probe. The performance intuitions from 1.7 transfer directly to query planning.
- TraceLab v0 (1.9) — the CSV blob whose debts this lesson starts repaying. The "schema in nobody's head" debt from that lesson's table is now resolved: we have a schema, it's typed, and it enforces its own invariants.
- Databases II: Queries, Indexes, and the Planner (3.5) — the schema you just built is the substrate for 3.5's queries. Without the right schema, the right query is impossible; without the right indexes, the right query is unbearably slow. That lesson picks up exactly here.
Data modeling is the discipline that makes everything downstream tractable. A well-normalized schema with enforced constraints means every query you write can trust the data it reads. Trust is not free — you earn it by choosing the right structure and encoding the right invariants before writing a line of application logic. The schema is the contract; the database engine is the enforcer.
GLOSSARYKey terms introduced
- Entity
- A distinct, independently-existing thing a system tracks — runs, agents, artifacts. Each entity becomes a table.
- Cardinality
- The numeric ratio of a relationship: one-to-one, one-to-many, or many-to-many. It determines where the foreign key lives.
- Primary key (PK)
- A column (or column set) that uniquely identifies each row in a table, enforcing uniqueness and non-nullability.
- Foreign key (FK)
- A column that must contain a value matching a primary key in another table. The database enforces referential integrity on every write.
- Normalization
- The process of organizing a schema so that every fact is stored exactly once, eliminating update, insert, and delete anomalies.
- Update anomaly
- When the same fact is duplicated across rows, a single logical change requires multiple physical updates — and a missed update leaves the database inconsistent.
- Referential integrity
- The guarantee that every foreign key value points to an existing primary key — no row references a parent that doesn't exist.
- Surrogate key
- A synthetic, database-generated identifier (integer or UUID) used as a primary key, decoupled from any business meaning and therefore stable across renames.
ACTIVE RECALLCheck your understanding
Answer in your own words first, then reveal.
1What is a delete anomaly, and which TraceLab v0 design decision causes one?›
A delete anomaly happens when deleting one entity accidentally destroys information about an unrelated entity. In TraceLab v0, the agent configuration is stored as repeated text values on every run row. If you delete all runs for agent "PPO-v7", the config for that agent vanishes — even if you wanted to keep it on record. The fix: agent configs live in their own agent table, and deleting a run's rows leaves the agent untouched.
2Why is ON DELETE RESTRICT on the agent foreign key and ON DELETE CASCADE on metric and artifact foreign keys the right choice for TraceLab?›
RESTRICT on agent means you cannot delete an agent that has runs pointing to it — you must explicitly reassign or delete those runs first. This prevents accidentally orphaning historical run data. CASCADE on metric and artifact means deleting a run automatically deletes all its metrics and artifacts — since a metric without its parent run is meaningless context-free data. The asymmetry reflects the asymmetry in the relationship: runs depend on agents, but metrics have no meaning without their run.
3What is Third Normal Form (3NF) actually preventing, in plain terms?›
3NF prevents a fact in one column from being determined by another non-key column — what's called a transitive dependency. In practice: if you stored an agent's architecture type directly on the run row, changing the architecture for an agent would require updating every run row. 3NF forces you to put that fact in the agent table once, and point to it with a foreign key. The principle is: every non-key column should describe the key, the whole key, and nothing but the key.
4The metric table has a UNIQUE(run_id, name, step) constraint and an INSERT ... ON CONFLICT DO NOTHING pattern. What problem does this solve that a transaction alone cannot?›
A transaction ensures atomicity — either the whole insert succeeds or nothing does. But if the connection drops after the insert commits and before the client receives the confirmation, a naive retry will insert the same row twice. The UNIQUE constraint plus ON CONFLICT DO NOTHING makes the operation idempotent: re-sending the same metric at the same step simply does nothing. The unique constraint is a uniqueness invariant that runs forever, in all callers — not a check any single piece of application code remembers to do.