SQLアンチパターン6章 ポリモーフィック関連

概要

本記事では、SQLアンチパターンで紹介されているポリモーフィック関連を概要レベルで紹介する

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

1つの子テーブルから複数のテーブルを参照する関連

ポリモーフィック関連を用いる場合、以下の考えに従いDBを定義する

1. 複数のテーブルへの参照のために用いるカラムを用意する
2. 参照しているテーブルを識別するためのカラムを用意する

ポリモーフィック関連 具体例

バグや機能に対するコメントを一つのテーブルに集約したい場合を考える

具体的には、以下のようなDB定義となる

問題点

以下の三つの問題点が存在する
(+αがついているものは、書籍には記載されていないもの)

1. +α一つのカラムに複数の関心事が混入する
2. 外部キーが定義できない
3. データに混入したメタデータの存在が保証できない

1. +α一つのカラムに複数の関心事が混入する

ポリモーフィック関連では参照するテーブルをissue_typeで管理し参照するレコードをissue_idで管理していた

だが、この管理方法だと以下の問題が発生する

1. issue_idという1つのカラムから、BugとFeatureという異なる関心事(domain)を参照している
   • 列が分割可能なdomainから値を参照しているため、第一正規系が要求する原子性(atomic)の定義を満たさない
     • atomicとは、小さな断片に分割できないことである
2. 参照先テーブルをissue_typeというカラムの値に依存している
   • 第三正規系では推移的関数従属を要求するため、非候補キー属性からは、参照先テーブル主キーのみを参照すればカラムがわかるが、その定義を満たさない
     • comment_id=>issue_id=>(参照する親テーブルのカラム)という関係が保持されない

2. 外部キーが定義できない

Commentsテーブルのissue_idは二つのテーブルを親テーブルに持ち、issue_typeの値に従い参照するテーブルを変更する必要がある

だが、SQLの外部キー制約では上述のような挙動を設定することが出来ない。

そのため、参照整合性を維持はDBはできず、アプリケーションで行う必要がある(※1)

3. データにメタデータが混入する

issue_typeにはテーブル名(メタデータ)が記載されているが、データベースにその名前に対応するテーブルが実在するかは保証されない

発見方法

以下のような事象が見られたとき、ポリモーフィック関連が導入されている可能性が高い

1. 外部キーを宣言できない場合
2. どのテーブルを参照しているのかを示すカラムが存在する場合
   • 上でいう、issue_typeのようなカラムが存在する場合
3. あるテーブルから複数の「あらゆる」リソースへと関連付けを行っている場合
   • 上のような無限の柔軟性がある場合、このようなポリモーフィック関連(やEAV)が導入されている可能性がある

ポリモーフィック関連を用いてもいい場合

以下の場合は2つを両方とも満たす場合、ポリモーフィック関連を用いることを検討してもよい

しかし、解決策に記載されている設計のほうが好ましい

1. ORMがポリモーフィック関連をサポートしている場合
   • HibernateやRailsはサポートしている
2. 設計時期のずれなどにより、子側のテーブルを設計/作成する時点で存在しない親テーブルとの関連を作成するとき
   • つまり、最終的にいくつの親テーブルと関連を持つかがわからない場合

解決法

大きく以下の4つが解決方法となる
それぞれについて記載していく
(+αがついているものは、書籍には記載されていないもの)

1. 連関エンティティの作成(参照の逆転)
2. 親テーブルの作成(クラステーブル継承)
3. +αテーブルの分割
4. +α参照するテーブルの数だけカラムを用意する

1. 連関エンティティの作成(参照の逆転)

ポリモーフィック関連が発生している場合には、本来あるべき関連がさかさまになっている

先ほどの例だと、CommentsテーブルはBugsテーブルとFeatureRequestsテーブルを参照している

しかし、本来的にはBugsとFeatureRequestsがCommentsを参照している状態であるため、関係が逆転している

そのため、BugsとFeatureRequestsがCommentsを参照するようにテーブル定義を変更する

具体的には、関連付けテーブル(連関エンティティ)を作成する

メリット

1. 参照整合性を担保できる
2. データベースにメタデータを格納する必要がない
   • issue_typeカラムへのデータ挿入など、関連付けの管理に関する作業をアプリケーションに依存しない
3. 1つのCommmentsが各テーブルから1回のみに参照されていることを担保するよう制約を付与できる
   • 連関エンティティのcomment_idにUNIQUE制約をつければ実現可能

デメリット

1. 望まない関連付けが付与されてしまう可能性がある
   • 上の例でいうと、BugsCommentsとFeaturesCommentsの両方から参照されるCommentsの存在を否定できない

2. 親テーブルの作成(クラステーブル継承)

共通の基底テーブルを定義できる場合、基底テーブルの外部キーをCommentsテーブルに挿入することでポリモーフィック関連を解消することができる

メリット

1. 参照整合性を担保できる
2. データベースにメタデータを格納する必要がない
   • issue_typeカラムへのデータ挿入など、関連付けの管理に関する作業をアプリケーションに依存しない
3. すべてのテーブルをissue_idを用いて結合できる
   • クエリーが比較的自由に記載することが出来る
4. 基底テーブルを継承したテーブルの追加が容易
   • 紐づくテーブルが頻繁に追加される場合に効果的

デメリット

1. 望まない関連付けが付与されてしまう可能性がある
   • 1つのcommentがBugsとFeatureRequestsに紐づく可能性がある

3. +αテーブルの分割

要件的にcommentsを一つのテーブルに保存する必要がない場合、Commentsテーブルを分割してしまえばよい。

メリット

1. 参照整合性を担保できる
2. データベースにメタデータを格納する必要がない
   • issue_typeカラムへのデータ挿入など、関連付けの管理に関する作業をアプリケーションに依存しない
3. 望まない関連付けが付与されてしまう可能性がない
   • 1つのcommentがBugsとFeatureRequestsの両方に紐づくことがない

デメリット

1. 基底テーブルが存在する場合と比較し、クエリーの発行が複雑になる可能性がある
2. 基底テーブルが存在する場合と比較し、テーブルの追加が困難

4. +α参照するテーブルの数だけカラムを用意する

シンプルな考え方として、参照するテーブルの数だけ外部キーを用意すればよい

メリット

1. 参照整合性を担保できる
2. データベースにメタデータを格納する必要がない
   • issue_typeカラムへのデータ挿入など、関連付けの管理に関する作業をアプリケーションに依存しない

デメリット

1. 望まない関連付けが付与されてしまう可能性がある
   • 1つのcommentがBugsとFeatureRequestsに紐づく可能性がある
   • CHECK制約で排除することは可能(でも個人的にはやりたくない)

結論

ポリモーフィック関連を見つけたときには、要件に基づき上に紹介した解決策を選択する
選択の順序は以下のページも参考になるので、参考にしてほしい

参考資料

SQLアンチパターン
https://www.slideshare.net/hironorimiura/sql-34880588
https://stackoverflow.com/questions/441001/possible-to-do-a-mysql-foreign-key-to-one-of-two-possible-tables