概要

複数のテーブルに対し、多対1でテーブルが関係付くときのテーブル設計のパターンについてまとめる。

データ設計

以下のようなケースのデータ設計を例とする。　

commentsがissues、pullrequestsのどちらに対しても多対一で関係付くようなケース。

commentsにtarget_tableとtarget_idというカラムを追加し、issuesとpullrequestsのどちらに結びつくか判断させようとするテーブル設計。

SQLアンチパターンではアンチパターンの一つとして取り上げられている。

target_idがtarget_tableを見ないとissuesとpullrequestsのどちらに関連付くかわからないため、外部キー制約が使えない。
したがって、このパターンではテーブル間の整合性保持はアプリケーションのロジックに依存することなる。

LaravelやRailsのORMではポリモーフィック関連がサポートされているので実装が楽なので、このようなパターンを検討する余地はゼロではないが、なるべく避けたいパターンではある。

issuesとpullrequestsに交差テーブルを用意して、外部キー制約を使えるようにするパターン。

issuesとissues_commentsは1対多、issues_commentsとcommentsは多対1となる。pullrequestsに関しても同様。

アプリケーションの要件次第ではあるが、1コメントがissuesとpullrequestsのどちらかだけに関連付くようにという制約を保証できない。

外部キーが使えるため、ポリモーフィック関連よりは整合性を保つことができる。　

issues 、pullrequests、commentsの共通の親となるテーブルを用意するパターン。　

postsはクラステーブル継承の考え方に基づいて定義するのが良さそう。（要は基底クラスと考える）
（参考：単一テーブル継承・クラステーブル継承・具象クラス継承について
PofEAA）

issuesとpostsが1対1、postsとcommentsが1対多で関連付く。pull_requestsも同様。
postsとcommentsは1対多で関連付く。

1コメントは1postsに関連付くという制約を保証できるが、issuesとpullrequests のどちらかだけに関連付くという制約を保証できない。

これはそもそもの前提を疑う話ではあるが、comments を1つのテーブルにまとめておくのではなく、別々のcommentsテーブルをそれぞれ用意してテーブルを分割しておけば良いのではないかというパターンである。

アプリケーション側のロジックに依存することは、ヒューマンエラーの可能性を高めるので、テーブル構造にロジックを依存させる設計方針が基本的には良いパターンではないかと思う。
アプリケーションの要件に加えて、クエリの気持ちを考えて最適なパターンを選択できるようにしたい。