A comments table stores commentable_type ('article', 'photo', 'video') alongside commentable_id to point at whichever parent the comment belongs to. What does that design cost, and what alternatives enforce the relationship properly?

## The pattern A **polymorphic association** stores a discriminator plus an identifier so one child table can attach to several unrelated parents: ``` comment(comment_id, commentable_type, commentable_id, body, ...) ``` where `commentable_type` is `'article'`, `'photo'` or `'video'`. It is popular because it looks economical - one table, one code path - and several ORMs make it a one-line declaration. ## The core defect A foreign key constraint names its target table when it is declared. Here the target is decided by a column value at runtime. There is therefore **no foreign key**, and everything a foreign key provides is gone: - **Orphans are unavoidable.** Delete an article and its comments remain, pointing at an id that no longer resolves. Nothing raises an error. The rows accumulate, consume storage, appear in counts, and eventually surface as a crash or a blank screen. - **No referential actions.** `ON DELETE CASCADE` and `ON DELETE RESTRICT` do not exist for this relationship, so every deleter must remember to clean up children, in the right order, in every code path. - **The discriminator is unvalidated.** Nothing constrains `commentable_type` to real table names unless you add a check listing them - which then needs updating alongside every new type, in a place unrelated to the tables themselves. - **Id collision across types.** `commentable_id = 42` is meaningful only together with the type; a bug that loses or defaults the type silently attaches a comment to the wrong entity, and the values look perfectly valid. ## Query consequences A composite index on `(commentable_type, commentable_id)` makes lookups by parent fast, so the problem is not raw access speed. The problem is joins. Fetching comments with their parents requires either a union of per-type joins or an application loop that fetches each parent by type - the classic N+1. Cross-type reporting ("most-commented items") means unioning the per-type shapes, and each new parent type edits every such query. Optimizers cannot use foreign-key knowledge here either, so estimates for these joins are worse. ## Alternative 1: exclusive arc Give the child one nullable foreign key per possible parent and require exactly one to be populated: ``` comment(comment_id, article_id NULL, photo_id NULL, video_id NULL, body, CHECK (num_nonnulls(article_id, photo_id, video_id) = 1)) ``` Every reference is a real foreign key: orphans are impossible, cascades work per parent, and joins are ordinary left joins that a planner understands. The costs are width and edit cost - each new parent type adds a column, widens the check and touches queries that enumerate the columns. It is the right choice when the number of parent types is small and unlikely to grow, say two to four. ## Alternative 2: shared supertype table Introduce a table representing "things that can be commented on": ``` commentable(commentable_id PK, kind) article(article_id PK REFERENCES commentable(commentable_id), ...) photo (photo_id PK REFERENCES commentable(commentable_id), ...) comment(comment_id PK, commentable_id REFERENCES commentable(commentable_id), ...) ``` Each concrete type takes its identity from the shared table, so a comment's single foreign key is fully enforced and cascades work. Adding a fourth parent type requires no change to `comment` at all - the extensibility the polymorphic version promised, now with integrity. Costs: one extra table, one extra join to reach the concrete parent, identity allocated centrally (a possible insert hotspot at very high rates), and a modest amount of ceremony that has to be maintained - if some path inserts an article without its `commentable` row, the model breaks, so the concrete tables must derive their key from the shared table rather than allocate their own. This is the shape relational modelling has always called a supertype/subtype hierarchy, and it is the general answer when the set of parent types is open. ## Alternative 3: one child table per parent `article_comment`, `photo_comment`, `video_comment`, each with a plain foreign key. Constraints are trivial, per-type queries are the simplest possible, and each table can diverge if the types genuinely need different columns. The cost is duplicated structure and index definitions, and cross-type queries become unions. It suits cases where the child rows are high-volume and per-type behaviour differs anyway. ## Choosing - Few, stable parent types, child rows want one narrow table: **arc**. - Open-ended parent types, integrity required: **supertype**. - High volume per type with diverging attributes: **table per parent**. - Purely non-critical attachments where orphans are harmless and cross-type volume is huge: the polymorphic pair may be tolerable, but only with an explicit orphan-sweeper and a check constraint on the discriminator, and with the decision documented as a deliberate trade. ## Migrating an existing polymorphic table Add the new structure alongside, backfill from the type/id pair, dual-write, move readers per type, then drop the old columns. Backfill is where the accumulated damage shows up: rows whose parent no longer exists and rows with unknown type values have to be triaged before any constraint can be created. That count is also the best argument for the change - it is the number of broken references the design has been silently accepting.