minte9

LearnRemember   ANTIPATTERNS

Context

Bugs or Features

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

Dual-Purpose Foreign Key

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.

Querying

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

Intersection Tables

Create a separate intersection table for each parent table. 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.

 
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;    

Super table

Solution 2

In OOP polymorphism, two subtypes can be referenced similarly.

 
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. You can retrieve the referenced bug or feature 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;