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.
| | CREATE TABLE Comments ( |
| | comment_id SERIAL PRIMARY |
Get SQL Antipatterns now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
