Polymorphic Association

This smell occurs when a table uses a multi-purpose foreign key.

Relational database schema does not allow us to declare polymorphic association. However, many times developers define an additional column in a table as a tag to realize a polymorphic association. This arrangement makes it difficult to query the table and compromises readability and understandability.

Example

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)
);

Reference

B. Karwin, “SQL Antipatterns: Avoiding the Pitfalls of Database Programming”. Pragmatic Bookshelf, 2010.