SQL

SQLアンチパターン：ポリモーフィック関連について整理

どうも、シローです。

今回は"ポリモーフィック関連"というアンチパターンについて整理したいと思います。

ポリモーフィック関連とは

ポリモーフィック関連とは外部キーは一つのテーブルに対して行うという原則を破って、

複数のテーブルへの参照キーを格納したテーブル構造のことを指します。

例えば、

  • コメントデータを格納したい
  • コメント(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を持つレコード間で参照整合性が間接的に取れています。

まとめ

  • 複数のテーブルへの参照キーは外部キー制約による参照整合性を捨てている
  • 複数のテーブル間との参照整合性を保証する設計方法としては、交差テーブルや共通の親テーブルを用いれば良い
SQLアンチパターン

本書はDB設計やSQL記述の際に避けるべき事柄を1章で1つ、25個紹介する書籍です。
リレーショナルデータベースを中心に据えたシステム開発には、様々な場面で陥りやすい失敗（アンチパターン）があります。
本書はデータベース論理設計、データベース物理設計、クエリの記述、アプリケーション開発という4つのカテゴリに分かれて、それぞれの分野におけるアンチパターンを紹介し、失敗を避けるためのより良い方法を紹介します。
複数の値を持つ属性や再帰的なツリー構造の格納から、小数値の丸めやNULLの扱いに起因する問題、全文検索やSQLインジェクション、MVCアーキテクチャなど、実践的かつ幅広いトピックを網羅します。
データベースに関わるすべてのエンジニア必携の一冊です。

-SQL

© 2023 Shiro's secret base