【SQL】アンチパターン６章：ポリモーフィック

おことわり

本記事は自分が理解するために説明を増やして書いています。間違いがあればご指摘ください

目的：複数の親テーブルを参照したい

• あなたは、ユーザが「Bug」や「Feature Request」「Task」などのチケット管理システムを作ります。
• どのタイプのチケットにもコメント機能を実装します

とりあえずカラムでTypeを追加するか...

CREATE TABLE Comments (
    comment_id SERIAL PRIMARY KEY,
    issue_type VARCHAR(20) NOT NULL, -- Bugs or Feature Request or Task
    issue_id BIGINT UNSIGNED NOT NULL,
    commen_date DATETIME,
    comment TEXT,
    ...
)

アンチパターン：二重目的の外部キーを使用する

外部キー制約を定義できないですよね

• FOREGIN KEYを定義できない

クエリ実行

• どのテーブルにどのタイプがあるかわからないので、全て外部結合（OUTER JOIN）をしなければならない

SELECT *
FROM Comments AS c
    LEFT OUTER JOIN Bugs AS b
        ON b.issue_id = c.isssue_type 
            AND c.issue_type = `Bugs`
    LEFT OUTER JOIN FeatureRequests AS f
        ON f.issue_id = c.issue_type = `FeatureRequests`;

解決作：リレーションシップを単純化する

参照を逆にする

• 本来あるべき関係が逆さまになっている

交差テーブルの作成

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

こうすることで外部キーを張ることができました。

SELECT *
FROM BugComments AS bc
    INNER JOIN Comments AS c
        ON bc.comment_id = c.comment_id
WHERE
    bc.issue_id = 24

ただ同様に全ての項目に対してテーブルを作る必要があります。

共通の親テーブルの作成

CREATE TABLE Issues (
    issue_id SERIAL,
    ...
);

CREATE TABLE Bugs (
    issue_id BIGINT UNSIGNED PRIMARY KEY,
    FOREIGN KEY (issue_id) REFERENCES Issues (issue_id),
    ...
);

最後に

写経すらできていないですが、改めて読み直して整理します・・・