ExpertRefresh

Dual FK

1) You can declare a foreign key to reference




2) With polymorthic association on table Comments







Polymorthic association

CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY, bug_id BIGINT UNSIGNED NOT NULL, author_id BIGINT UNSIGNED NOT NULL, comment_date DATETIME NOT NULL, comment TEXT NOT NULL, FOREIGN KEY (author_id) REFERENCES Accounts(account_id), FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id) ); Bug-tracking application. Let’s allow users to make comments on bugs. So, there’s a one-to-many relationship between Bugs and Comments. However, you might have two tables you can comment on. 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.

Antipattern: Use Dual-Purpose Foreign Key

A solution for these cases has become popular enough to be given a name, Polymorphic Associations. To make Polymorphic Associations work, you must add 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, author BIGINT UNSIGNED NOT NULL, comment_date DATETIME, comment TEXT, FOREIGN KEY (author) REFERENCES Accounts(account_id) ); You can see one difference immediately: the foreign key declaration for issue_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;

Another solution: 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;