Polymorphic Associations
You'll learn to
- -Model a column (like comments or likes) that can belong to more than one parent entity type
- -Weigh a polymorphic type+id column pair against separate join tables per parent type, and know when each earns its complexity
A polymorphic association is a relationship where one table can belong to more than one type of parent - a Comment might attach to a Post, a Photo, or a Video. A plain foreign key cannot express "this points at a row in one of several different tables," so this needs a deliberate structural choice.
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 and lets every comment live in one table, one query away regardless of what it is attached to. The real cost: the database cannot enforce commentable_id as a genuine foreign key, since it might point at posts, photos, or videos depending on the row - referential integrity for this column has to be enforced in application code instead of the schema, which is a meaningfully weaker guarantee than everything covered so far in this course.
Approach 2: A Separate 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, database-enforced foreign key integrity for every parent type, at the cost of one extra table per parent type, and a query that wants "all comments regardless of parent type" now needs to union across every join table instead of a single, simple SELECT.
Choosing Between Them
The trade-off mirrors a familiar shape from Phase 1: the type-column approach is simpler and more flexible (adding a new commentable type needs no schema change), similar to how a loosely-typed design is easy to extend but gives up compile-time guarantees; the join-table approach trades that flexibility for the database actively enforcing correctness, similar to favoring strong interfaces over duck typing. The practical guidance: if the set of parent types is small, stable, and known upfront, and referential integrity genuinely matters (comments should never point at a deleted post), lean toward separate join tables. If new parent types are added frequently or the association is lower-stakes, the type-column approach is a reasonable, pragmatic trade.
The type-column approach's biggest hidden risk: nothing stops commentable_type from containing a typo ('pots' instead of 'posts') or commentable_id from pointing at a row that has since been deleted - both are silent failures the database will never catch, unlike 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.