Skip to content
build-3000
databases

Relational Data Modeling

Tables that reference each other - foreign keys, joins and migrations that do not break things.

Before this: Servers and Backends

Objective

Model multi-user data as related tables, enforce integrity with foreign keys, evolve schema safely through migrations, and prove relationships with join queries.

Vocabulary

The words to use when directing your agent.

primary key
The unique id identifying each row in a table.
foreign key
A column referencing another table's primary key, linking rows together.
one-to-many
A relation where one row owns many others - one user, many expenses.
join
A query combining rows from related tables.
migration
A versioned, repeatable script that changes the database schema.
referential integrity
The guarantee that a foreign key always points at a real row.

Understand it

From flat file to relations

Level 1 stored expenses anonymously because there were no users. Multi-user changes everything: WHOSE expense becomes part of the data itself. That single foreign key unlocks permissions, filtering, and every team feature you will ever build.

Integrity at the database level

App code can have bugs; constraints cannot be bypassed by a forgotten check. A foreign key constraint makes an orphan row physically impossible - the database itself refuses. Defense in depth starts with letting the storage layer carry some of the weight.

Core concepts

Reference, never duplicate

When expenses belong to users, each expense stores user_id - not a copied username. One source of truth: change the username once, everywhere correct. Duplicated facts drift out of sync and lie eventually.

Migrations are version control for schema

Schema changes ship as ordered scripts that run the same way everywhere - your laptop, teammates, production. Hand-tweaking a live database outside migrations is how environments silently diverge.

Examples

The join that proves the model

SELECT expenses.amount, users.name FROM expenses JOIN users ON expenses.user_id = users.id - one query, both tables, each expense displayed beside its owner's name. If your join works, your keys are right.

Constraint refusing an orphan

Inserting an expense whose user_id is 999 when no such user exists fails with a foreign key error. That refusal is a feature: corrupted relationships cannot enter the database even through buggy code.

Common mistakes

Common mistake

Trusting the application to keep references valid without DB constraints.

Fix

Declare foreign key constraints in migrations; let the database refuse impossible relationships.

Common mistake

Editing schema by hand in a tool while migrations say otherwise.

Fix

Every schema change goes through a migration file - no exceptions, including 'quick experiments'.

Working with your AI agent

Make your agent draft the ER picture in text before coding: tables, columns, types, which columns reference which, and what cascades on delete. Approve the picture; the migration files then become mechanical transcription.

Build it

The module’s real project. Your agent builds; you direct and inspect.

Project: Data Model Upgrade

Migrate the Expense API to relational multi-user storage: users table, expenses owned via foreign key, migration scripts, and join-proofs that relationships resolve correctly.

Requirements

  • users table (id, email unique, name, created_at) and expenses.user_id foreign key added via migration scripts
  • Foreign key constraint actively prevents orphan expenses
  • Migration runs cleanly twice: once on fresh database, once upgrading existing Level-1 data
  • New endpoint GET /users/:id/expenses returns that user's expenses with their name via a join
  • Rollback path: down-migration or documented restore procedure exists and was tested

Context for your agent

Upgrade my Expense API's storage to relational multi-user modeling. Write proper up/down migrations creating users and adding expenses.user_id with a foreign key constraint. Seed two demo users and reassign existing expenses sensibly during upgrade. Add GET /users/:id/expenses performing a real join returning expense rows plus owner name. Show me the ER outline first; implement only after approval. Prove constraint enforcement by attempting an orphan insert and showing me the database's refusal.

Verify it - twice

Check every item yourself first. Then hand your agent the prompt and compare verdicts.

Self-verified0/10
Functional
Architecture
Security
Testing
Production

Verification prompt - paste into your AI agent

Audit my Data Model Upgrade as a database engineer. Inspect migrations and run them yourself if able. Verify PASS or FAIL with evidence: (1) migrations are versioned and include a tested rollback, (2) users and expenses relate through a real enforced foreign key, (3) an orphan insert genuinely fails at the database level, (4) the join endpoint returns owner-enriched rows correctly, (5) existing data survived the upgrade intact. Then try three creative corruptions - duplicate email, self-referencing nonsense, deleting a referenced user - and report which the database refused versus which slipped through.

Run this against “Relational Data Modeling” after your own checklist pass. Where the agent and you disagree, investigate before moving on.

Review questions

Answer out loud. If you cannot, revisit before moving on.

  1. Draw your schema from memory: tables, key columns, arrows between them.

  2. What breaks first if foreign keys were removed but app code stayed the same?

  3. Why must schema changes ship as migrations rather than manual edits?

You can now

  • Design relational schemas with enforced integrity
  • Evolve databases safely through tested migrations
  • Prove relationships with joins instead of assumptions

Up next

Server-Side Validation and Errors