Polymorphic associations: one table referencing multiple tables

The Problem

Sometimes one table needs to relate to multiple other tables. A comments table might attach to posts, videos, and photos. This is a polymorphic association.

Pattern 1: Type + ID Columns

CREATE TABLE comments (
  comment_id  INT PRIMARY KEY AUTO_INCREMENT,
  body        TEXT NOT NULL,
  entity_type VARCHAR(50) NOT NULL,  -- 'post', 'video', 'photo'
  entity_id   INT NOT NULL,
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- No FK possible — cannot reference multiple tables from one column

Simple to implement. The downside: you cannot enforce referential integrity with a foreign key. The database cannot verify that entity_id 42 actually exists in the posts table when entity_type is 'post'.

Pattern 2: Separate Junction Tables

CREATE TABLE post_comments (
  comment_id INT NOT NULL,
  post_id    INT NOT NULL,
  PRIMARY KEY (comment_id, post_id),
  FOREIGN KEY (comment_id) REFERENCES comments(comment_id),
  FOREIGN KEY (post_id)    REFERENCES posts(post_id)
);

CREATE TABLE video_comments (
  comment_id INT NOT NULL,
  video_id   INT NOT NULL,
  PRIMARY KEY (comment_id, video_id),
  FOREIGN KEY (comment_id) REFERENCES comments(comment_id),
  FOREIGN KEY (video_id)   REFERENCES videos(video_id)
);

More tables, but full referential integrity. Use separate junction tables when data correctness is critical. Use the type+ID pattern when you need simplicity and are willing to enforce integrity in application code.