Unit 2 · Module 3

Relational Data

How tables connect, how to design them, and how schemas grow up

Where this module picks up

Module 1 set up the instinct: instead of repeating the same information in every place it's needed, store it once and point at it from anywhere else with an id. Module 2 introduced JOIN, the SQL move that stitches those pointed-at rows back together at query time.

This module is the design layer that sits between those two ideas. Knowing that a foreign key exists is one thing. Knowing where the foreign key should go, which tables to create in the first place, and when to break the rules on purpose is the part that turns relational data from a feature into a skill.

What the seven pages cover

The pages move from how relationships are shaped to how schemas are designed to how schemas evolve over time. By the end, the same foreign-key idea has been seen from every angle a working developer cares about.

  1. The Three Relationship Shapes. One-to-one, one-to-many, many-to-many. Where the foreign key actually lives in each, and why.
  2. Foreign Keys In Depth. What a foreign key actually is under the hood. The lesser-known fact that they don't have to point at a primary key. Where this is useful and where it's a trap.
  3. Reading A Schema Like A Map. Given a real schema diagram, what story does it tell? Central tables, leaf tables, join tables, and how to orient yourself in an unfamiliar database.
  4. The "Should This Be Its Own Table?" Test. The decision rubric for splitting versus combining. The cost of splitting too early, the cost of not splitting soon enough.
  5. Normalize, Then Denormalize On Purpose. The default rule (split everything) and the deliberate exception (un-split a specific thing because reads are hurting).
  6. Migrations Are Not Failure. Schema evolution as a managed practice, not a confession. What a real Supabase migration file looks like.
  7. Designing A Schema From A Product Description. The interactive page. Build a real schema one decision at a time.

Analogy: The map, the city, and the rebuild

A schema is a city. The first pass at it is your best guess about where the streets should go before anyone has moved in. Once people start living there, reality pushes back. The market wants to be on the other side of the park. The bus route needs a stop the original plan didn't have. The bridge needs to be widened.

Cities don't get torn down and rebuilt when this happens. They evolve, block by block, with engineers who understand why each change is being made. Schemas work the same way. The skill isn't getting the city perfect on day one. It's knowing how to evolve it without breaking what's already there.

A note before starting

Some of this module formalizes instincts you may already have without knowing the vocabulary for them. That's expected. The goal isn't to invent new intuition — it's to attach real names and real rules to the moves you'd already make on instinct, so they're recognizable when other developers talk about them.