0
Challenge · 21SQL III: Relationships & Joins
Modeling one-to-many
Schemaused in ~95% of apps
Every developer who learned to program before they learned SQL has, at some point, modeled "a user's posts" as an array field on the user: `post_ids: [1, 2, 7]`. It reads naturally — it's exactly how you'd shape the data in a JS object. It also has no foreign key, no constraint, and no way for the database to notice when it goes stale. One-to-many is the single most common relationship in every schema you will ever write, and getting the direction of the foreign key backwards is the mistake that quietly reintroduces every bug concept 20 just spent an entire lesson eliminating.
Core idea. In a one-to-many relationship, the foreign key always lives on the "many" side, pointing back at the "one" side — never the other way around. One profile has many posts, so `posts.user_id` points at `profiles.id`; a post can never need to point at more than one profile, and a profile row never needs to hold a list of anything. The test that tells you which side is "many": pick a single row on either side and ask how many rows on the other side it can legitimately be paired with, right now, forever. If the answer is "always exactly one," that side gets the foreign key.
poke it, drag it,
try to break it
try to break it
no slides here.
you drive.
you drive.
Playground, this isn't a screenshot
profiles (the "one")
carol has zero posts — a valid, ordinary one-to-many row with no children, nothing to model specially.
posts (the "many")
Why I switched to Postgres
user_id: alice ← references profiles.id
Draft: RLS notes
user_id: alice ← references profiles.id
Ships on Fridays
user_id: bob ← references profiles.id
↑ play a little first, then dig in