SQL Antipatterns: Avoiding the Pitfalls of Database Programming/

...

/

Antipattern: Use Dual-Purpose Foreign Key

Antipattern: Use Dual-Purpose Foreign Key

Let’s investigate how using a foreign key to make polymorphic association affects the database.

We'll cover the following...

Defining a Polymorphic Association

To make Polymorphic Associations work, we 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.

Press + to interact
CREATE TABLE Comments (
  comment_id          SERIAL PRIMARY KEY,
  issue_type          VARCHAR(20), -- "Bugs" or "FeatureRequests"
  issue_id            BIGINT UNSIGNED NOT NULL,
  author              BIGINT UNSIGNED NOT NULL,
  comment_date        DATETIME,
  comment             TEXT,
  FOREIGN KEY (author) REFERENCES Accounts(account_id)
);

We can see one difference immediately: the foreign key declaration for ...