Polymorphic Associations
You'll learn to
- -Recognize a polymorphic association: one table whose rows can belong to several different parent tables
- -Compare a type-plus-id column pair with a join table per parent, and name what each gives up
- -Know the typo and orphan risks of the type-column approach, and when the pragmatic choice is still right
A polymorphic association is a relationship where one table can belong to more than one kind of parent. A comment might be attached to a post, a photo or a video. A plain foreign key cannot say "this points at a row in one of several different tables", so this needs a deliberate choice about how to structure it.
An Analogy: The Sticky Note
Think of a sticky note that can be stuck onto a book, a photo or a door. The note has to say what kind of thing it is stuck to, and which one. That is the first approach. The second is to keep a separate pad of notes for books, for photos and for doors, so each pad only ever sticks to one kind of thing. The first is flexible but loose. The second is tidier but needs more pads.
Approach 1: A Type Column Plus a Generic ID
CREATE TABLE comments (
id INTEGER PRIMARY KEY,
body TEXT NOT NULL,
commentable_type TEXT NOT NULL, -- 'post', 'photo', 'video'
commentable_id INTEGER NOT NULL -- an id in whichever table commentable_type names
);This is compact. Every comment lives in one table, one query away whatever it is attached to. Adding a new commentable thing, such as playlists, needs no schema change. The real cost: the database cannot treat commentable_id as a foreign key, because it might point at posts, photos or videos depending on the row. Referential integrity for that column must be enforced in application code, which is a much weaker guarantee than everything else in this course.
Approach 2: A Join Table per Parent Type
CREATE TABLE comments (
id INTEGER PRIMARY KEY,
body TEXT NOT NULL
);
CREATE TABLE post_comments (
post_id INTEGER NOT NULL REFERENCES posts(id),
comment_id INTEGER NOT NULL REFERENCES comments(id),
PRIMARY KEY (post_id, comment_id)
);
CREATE TABLE photo_comments (
photo_id INTEGER NOT NULL REFERENCES photos(id),
comment_id INTEGER NOT NULL REFERENCES comments(id),
PRIMARY KEY (photo_id, comment_id)
);This restores real foreign keys for every parent. The cost is one extra table per parent type, and a query for "all comments regardless of parent" now has to UNION across every join table. One detail to watch: with the primary key shown, the same comment could be linked to two different posts. If a comment must belong to exactly one parent, make comment_id unique in each join table. Stopping a comment from being linked in both post_comments and photo_comments needs yet another check, which is part of why this approach is heavier.
Choosing Between Them
The trade-off echoes Phase 1. The type column is simpler and more flexible, a bit like a loosely typed design that is easy to extend but gives up compile-time guarantees. The join tables trade that flexibility for the database actively enforcing correctness, like preferring strong interfaces over duck typing.
- -Lean toward separate join tables when the parent types are few, stable and known upfront, and integrity truly matters (a comment must never point at a deleted post).
- -Lean toward the type column when new parent types appear often, or the association is lower-stakes.
The type-column approach has a hidden risk. Nothing stops commentable_type from holding a typo such as 'pots' instead of 'posts', or commentable_id from pointing at a row that has since been deleted. The database will never catch either, because neither is a real foreign key violation.
Interview Signal is part of Pro
See a real weak answer next to a real strong one for this exact topic.
Quiz is part of Pro
Test what you just read with a short quiz, and bank the XP.
Design The Wall (posts and media) in the LLD Lab's Social Network act.
Discussion0
Join the Discussion
Sign in to leave comments, reply to others, or like insights.
No comments yet. Be the first to start the thread!