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.
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.
Draw your schema from memory: tables, key columns, arrows between them.
What breaks first if foreign keys were removed but app code stayed the same?
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