どうも、シローです。
今回は"ポリモーフィック関連"というアンチパターンについて整理したいと思います。
ポリモーフィック関連とは
ポリモーフィック関連とは外部キーは一つのテーブルに対して行うという原則を破って、
複数のテーブルへの参照キーを格納したテーブル構造のことを指します。
例えば、
- コメントデータを格納したい
- コメント(Comments)はバグ(Bugs)や機能要望(FeatureRequests)に関連づけたい
といった場合、以下のような構成にしてしまうとポリモーフィック関連です。
問題点：外部キー制約を使うことができない
Comments.issue_id =>Bugs.bug_id
Comments.issue_id => FeatureRequests.feature_id
という参照になっており、Comments.issue_typeの取りうる値(Bugs、FeatureRequestsなど)によって、どのテーブルへの関連付けなのかを表しています。
外部キー制約は一つのテーブルしか親として選べないので、この構成では外部キーを設定することができないというのが問題となります。
解決策：交差テーブルや共通の親テーブルを作成する
"ポリモーフィック関連"にせずに複数のテーブルへの参照整合性を保証したい場合はざっくりと以下の２つの方法があります。
交差テーブルを作成する
N対Nの関係になるテーブル間の参照を作成するのに使われます。(こちらの記事でも紹介しています→https://shiro-secret-base.com/?p=890 )
交差テーブルを用いた例は以下のようになります。
BugsとComments間の参照を表すBugsComments、
FeatureRequestsとComments間の参照を表すFeaturesComments
がそれぞれ交差テーブルです。
共通の親テーブルを作成する
次に紹介するのは共通の親テーブルを用いた構成です。
IssuesがBugs,FeatureRequestsそしてCommentsへの親テーブルになっています。
これら３つのテーブルには全てIssues.issue_idが共通の外部キーになっています。
よって、共通のissue_idを持つレコード間で参照整合性が間接的に取れています。
まとめ
- 複数のテーブルへの参照キーは外部キー制約による参照整合性を捨てている
- 複数のテーブル間との参照整合性を保証する設計方法としては、交差テーブルや共通の親テーブルを用いれば良い
本書はDB設計やSQL記述の際に避けるべき事柄を1章で1つ、25個紹介する書籍です。
リレーショナルデータベースを中心に据えたシステム開発には、様々な場面で陥りやすい失敗（アンチパターン）があります。
本書はデータベース論理設計、データベース物理設計、クエリの記述、アプリケーション開発という4つのカテゴリに分かれて、それぞれの分野におけるアンチパターンを紹介し、失敗を避けるためのより良い方法を紹介します。
複数の値を持つ属性や再帰的なツリー構造の格納から、小数値の丸めやNULLの扱いに起因する問題、全文検索やSQLインジェクション、MVCアーキテクチャなど、実践的かつ幅広いトピックを網羅します。
データベースに関わるすべてのエンジニア必携の一冊です。