0

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.
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