Memory Refresh
SQLAntipatterns



Polymorphic Associations





Context: Users can make comments on bugs or features

There’s a one-to-many relationship between Bugs and Comments. Bugs and FeatureRequests are similar entities, although you might store them as separate tables. You’d like to store Comments in a single table regardless of issue (bug or feature). But you can’t declare a foreign key that references multiple parent tables. CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY, bug_id BIGINT UNSIGNED NOT NULL, author_id BIGINT UNSIGNED NOT NULL, ... FOREIGN KEY (author_id) REFERENCES Accounts(account_id), FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id) );

Antipattern: Use Dual-Purpose Foreign Key

A solution for these cases has become popular enough to be given a name, Polymorphic Associations. It adds an extra string column alongside the foreign key on issue_id (issue_type). CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY, issue_type VARCHAR(20), -- "Bugs" or "FeatureRequests" issue_id BIGINT UNSIGNED NOT NULL, ... FOREIGN KEY (author) REFERENCES Accounts(account_id) --FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id) ); You can see one difference immediately: the foreign key declaration for bug_id is missing. As a result, there is no enforcement of data integrity to ensure that the value in Comments.issue_id matches a value in the parent table. Querying a Polymorphic Association SELECT * FROM Bugs AS b JOIN Comments AS c ON (b.issue_id = c.issue_id AND c.issue_type = 'Bugs') WHERE b.issue_id = 1234; You run into a problem when a comment is associated with both tables Bugs and FeatureRequests. Only one of the parent tables will satisfy its join. SELECT * FROM Comments AS c LEFT OUTER JOIN Bugs AS b ON (b.issue_id = c.issue_id AND c.issue_type = 'Bugs') LEFT OUTER JOIN FeatureRequests AS f ON (f.issue_id = c.issue_id AND c.issue_type = 'FeatureRequests');

Solution: Creating Intersection Tables

Create a separate intersection table for each parent table, and in each intersection table include a foreign key to Comments. CREATE TABLE BugsComments ( issue_id BIGINT UNSIGNED NOT NULL, comment_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (issue_id, comment_id), FOREIGN KEY (issue_id) REFERENCES Bugs(issue_id), FOREIGN KEY (comment_id) REFERENCES Comments(comment_id) ); CREATE TABLE FeaturesComments ( issue_id BIGINT UNSIGNED NOT NULL, comment_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (issue_id, comment_id), FOREIGN KEY (issue_id) REFERENCES FeatureRequests(issue_id), FOREIGN KEY (comment_id) REFERENCES Comments(comment_id) ); This solution removes the need for the issue_type. You probably want each comment to pertain to only one bug or one feature request. CREATE TABLE BugsComments ( issue_id BIGINT UNSIGNED NOT NULL, comment_id BIGINT UNSIGNED NOT NULL, UNIQUE KEY (comment_id), PRIMARY KEY (issue_id, comment_id), FOREIGN KEY (issue_id) REFERENCES Bugs(issue_id), FOREIGN KEY (comment_id) REFERENCES Comments(comment_id) ); You can query comments given a specific bug or feature SELECT * FROM BugsComments AS b JOIN Comments AS c USING (comment_id) WHERE b.issue_id = 1234;

Solution 2: Common super-table

In OOP polymorphism, two subtypes can be referenced similarly because they implicitly share a common supertype. CREATE TABLE Issues ( issue_id SERIAL PRIMARY KEY ); CREATE TABLE Bugs ( issue_id BIGINT UNSIGNED PRIMARY KEY, FOREIGN KEY (issue_id) REFERENCES Issues(issue_id), . . . ); CREATE TABLE FeatureRequests ( issue_id BIGINT UNSIGNED PRIMARY KEY, FOREIGN KEY (issue_id) REFERENCES Issues(issue_id), . . . ); CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY, issue_id BIGINT UNSIGNED NOT NULL, author BIGINT UNSIGNED NOT NULL, comment_date DATETIME, comment TEXT, FOREIGN KEY (issue_id) REFERENCES Issues(issue_id), FOREIGN KEY (author) REFERENCES Accounts(account_id), ); Note that the primary keys of Bugs and FeatureRequests are also foreign keys. They reference the surrogate key value generated in the Issues table, instead of generating a new value for themselves. You can retrieve the referenced bug or feature request using a relatively simple query. SELECT * FROM Comments AS c LEFT OUTER JOIN Bugs AS b USING (issue_id) LEFT OUTER JOIN FeatureRequests AS f USING (issue_id) WHERE c.comment_id = 9876; Given a specific bug, you can retrieve its comments just as easily. SELECT * FROM Bugs AS b JOIN Comments AS c USING (issue_id) WHERE b.issue_id = 1234;