おことわり
本記事は自分が理解するために説明を増やして書いています。間違いがあればご指摘ください
目的：複数の親テーブルを参照したい
- あなたは、ユーザが「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), ... );
最後に
写経すらできていないですが、改めて読み直して整理します・・・
