ProjekinAja. Software Engineering Stage 3 · Systems Lesson 3.4
● Stage 3 · Lesson 3.4

Databases I: Relational Thinking

Your dict-of-dicts wants to be tables — schemas, keys, and normalization as anomaly prevention, not dogma.

⏱ ~20 min read 🗄️ Data modeling 🧱 TraceLab v2

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

🔑 Hold onto this

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:

A relationship describes how entities connect. But vague "connects to" language isn't enough — you need cardinality: the numeric ratio. There are three:

CardinalityWhat it meansTraceLab exampleHow it maps to tables
One-to-one (1:1)Each A has at most one B and vice versaA run has one configuration snapshotForeign key in either table, or merged
One-to-many (1:N)One A can have many B, but each B belongs to one AOne run has many metric readingsForeign key on the "many" side (metrics.run_id)
Many-to-many (M:N)A can have many B, and B can have many AA run can use many tags; a tag can label many runsJunction/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.

Agent arch + hyperparams Run experiment execution Metric name + value + step Artifact filepath + type 1:N 1:N 1:N many metrics & artifacts per run → FK on child table
TraceLab's core entities and relationships. Every arrow labeled 1:N means the "many" side holds a foreign key pointing back to the "one" side. No entity floats free.
💡 Nullable columns are a smell

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.

1

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.

2

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.

3

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.

4

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.

📖 Why surrogate keys beat natural keys

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:

AnomalyWhat happensTraceLab example
Update anomalyChanging a fact requires updating multiple rows; miss one and the DB has conflicting truthsAgent architecture "ResNet18" stored on every run row → rename it in 3 rows but not 47 → half the history now lies
Insert anomalyYou can't record a fact without recording an unrelated oneCan't register an agent config until it has run at least one experiment — the config has nowhere to live
Delete anomalyDeleting one entity accidentally destroys unrelated factsDelete 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:

💡 When to denormalize deliberately

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.

⚙ Interactive · Schema builder + anomaly detector

Click a field to place it in a table. Start from a preset or build from scratch. Then scan for update / insert / delete anomalies.

FIELD POOL (click to place)

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:

schema.sqlTraceLab v2
-- 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.

📖 JSONB: structured escape hatch

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 columnv2 tableWhat changed and why
timestamprun.started_atTyped as TIMESTAMPTZ — timezone-aware, not a string. Sortable, comparable, range-queryable without parsing.
run_namerun.name + run.idHuman name decoupled from identity. The surrogate ID never changes even if you rename the run.
metric1, metric2, …metric rowsVariable-length columns become rows. Each metric is independently queryable, aggregatable, and can have its own timestamp and step counter.
(implicit) agent configagent tableAgent config was repeated on every row of a multi-run experiment. Now it lives once with an FK reference — no update anomaly possible.
(missing) artifactsartifact tablev0 had no place for checkpoint paths or plots. The FK relationship means artifacts are tightly linked to their run and cleaned up together.
(missing) statusrun.statusA 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:

tracelab/db.pyv2 metric ingest
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.

🔑 The v0 debt this repays

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

✗ Treating the data model as a detail you'll sort out later

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?"

✗ Using a UUID primary key where a sequence would do — or vice versa

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.

✗ Normalizing everything to the point of join hell

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.

Next: queries, indexes, and how the planner decides what to do →