Antipattern: Use Dual-Purpose Foreign Key

A solution for these cases has become popular enough to be given a name, Polymorphic Associations. This is also sometimes called a promiscuous association, because it can reference multiple tables.

Defining a Polymorphic Association

To make Polymorphic Associations work, you must add an extra string column alongside the foreign key on issue_id. The extra column contains the name of the parent table referenced by the current row. In this example, the new column is called issue_type, and contains either Bugs or FeatureRequests, corresponding to the names of the two possible parent tables in this association.

Polymorphic/anti/comments.sql
 
CREATE​ ​TABLE​ Comments (
 
comment_id SERIAL ​PRIMARY​ ​KEY ...

Get SQL Antipatterns now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.