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

# 概要

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

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

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

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

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

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

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

![er.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/374329/c41fdf1f-c747-d37f-8100-1b4f8bfe00bd.png)

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

![er図.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/374329/88797122-e977-de6b-1799-8a0f47ccb631.png)

## 問題点

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

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

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

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

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

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

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

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

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

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

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

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

## 発見方法

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

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

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

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

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

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

## 解決法

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

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

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

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

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

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

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

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

![pattern1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/374329/49f4f0c3-0ed3-7c99-09ae-484823b8f852.png)

#### メリット

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

#### デメリット

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

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

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

![pattern4.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/374329/c1cc0edd-cb85-278a-ef31-89bbc4515a98.png)

#### メリット

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

#### デメリット

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

#### 3. **+α**テーブルの分割

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

![pattern2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/374329/c8ca707a-3eae-c0fd-07b7-fc50a46a3ec2.png)


#### メリット

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

#### デメリット

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


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

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

![pattern3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/374329/8d7cf4a8-29da-6798-3af5-4fcfb42cca73.png)

#### メリット

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

#### デメリット

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

## 結論

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

https://spice-factory.co.jp/development/has-and-belongs-to-many-table/

## 参考資料

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