O'Reilly logo

SQL Antipatterns by Bill Karwin

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required