0
Challenge · 22SQL III: Relationships & Joins
Many-to-many & the join table
Schemaused in ~85% of apps
A profile can like many posts. A post can be liked by many profiles. Try to model that the way concept 21 just taught you — put a foreign key on the "many" side — and you discover there is no many side. Both directions are many. Put `liked_post_id` on `profiles` and a profile can like exactly one post, ever. Put `liked_by` on `posts` and a post can have exactly one liker. Either way you have modeled something smaller than what the product needs, and it will look fine in a demo with one user and fall over the moment a second person likes anything.
Core idea. A many-to-many relationship cannot be represented with a foreign key on either existing table — it needs a third table of its own, conventionally called a join table (or bridge, or associative table), holding one foreign key to each side. `likes(post_id, user_id)` is two ordinary one-to-many relationships sharing a table: many likes per post, many likes per profile. Making the pair `(post_id, user_id)` the primary key isn't just an efficiency trick — it IS the business rule "a profile can like a given post at most once," enforced by Postgres, not remembered by application code.
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
likes (post_id, user_id)
(1, bob)
(3, carol)
(3, bob)
one row per like — the primary key is the whole pair
posts
Click a profile or a post. Same three bridge rows, read in whichever direction you asked the question.
↑ play a little first, then dig in