ポリモーフィック関連
子テーブルが複数の親をテーブルを参照するアンチパターン
＃アンチパターン例
以下の例では親テーブルとしてcomicテーブルが定義されており、漫画本１冊についてcommentテーブルで本のコメントがつけられている状態である。
comic
| comic_id | comment_id | name |
|---|---|---|
| 1 | 1 | エンジニアになろう１巻 |
| 2 | 2 | エンジニアになろう２巻 |
| 3 | 3 | エンジニアになろう３巻 |
comment
| comment_id | comment |
|---|---|
| 1 | なかなか好調な滑り出し |
| 2 | ２巻からこの盛り上がりはすごい！ |
| 3 | どうしてこうなった.... |
アンチパターンを用いた場合、親テーブルがcomicテーブル以外にも存在し、それぞれがcommentテーブルでコメントを紐づけている状態を指す。
comic
| comic_id | comment_id | name |
|---|---|---|
| 1 | 1 | エンジニアになろう１巻 |
| 2 | 2 | エンジニアになろう２巻 |
| 3 | 3 | エンジニアになろう３巻 |
magazine
| magazine_id | comment_id | name |
|---|---|---|
| 1 | 4 | 週刊エンジニア７月号 |
| 2 | 5 | 週刊エンジニア８月号 |
| 3 | 6 | 週刊エンジニア９月号 |
comment
| comment_id | comment |
|---|---|
| 1 | なかなか好調な滑り出し |
| 2 | ２巻からこの盛り上がりはすごい！ |
| 3 | どうしてこうなった.... |
| 4 | あの超大御所エンジニアがこんなことを..... |
| 5 | あの有名エンジニアがそんなことを..... |
| 6 | あのエンジニアがあんなことを..... |
このようなテーブル構成の場合、commentテーブルのcomment_idに外部キーを設定しようとしても親テーブルが複数あるため外部キーを設定できない状態となる。
そもそも外部キーって？
他のテーブルのデータにを参照（依存）するようにカラムにつける制約。
- 親テーブル ⇨ 子テーブルに参照されるテーブル
- 子テーブル ⇨ 親テーブルを参照するテーブル
具体的に設定するとどうなるのか以下の例を見て欲しい。
外部キーは、親テーブルに存在しない値の登録ができない
親テーブル
|book_id (PK)|title|
|:---|:---|:---:|
|1|エンジニアの本１|
|2|エンジニアの本２|
|3|エンジニアの本３|
子テーブル
| comment_id (PK) | book_id (FK) | comment |
|---|---|---|
| 1 | 1 | とても良い |
| 2 | 2 | まあまあ良い |
| 3 | 3 | どちらかと言えば良い |
| 4 | 4 | どちらかと言えばよくない |
↑ 追加したい
この場合、子テーブルに４つ目のデータを登録しようとしましたが親テーブルに「book_id が 4」のデータがないためデータを登録できない。
外部キーは、子テーブルに存在する値の削除できない
親テーブル
|book_id (PK)|title|
|:---|:---|:---|
|1|エンジニアの本１|
|2|エンジニアの本２|
|3|エンジニアの本３|
↑ 削除したい
子テーブル
| comment_id (PK) | book_id (FK) | comment |
|---|---|---|
| 1 | 1 | とても良い |
| 2 | 2 | まあまあ良い |
| 3 | 3 | どちらかと言えば良い |
この場合、親テーブルから「book_id が 3」のデータを削除しようとしましたが子テーブルで「book_id が 3」を参照しているため削除できない。
削除する場合、子テーブルから「book_id が 3」を参照しているデータを削除してから親テーブルのデータを削除する必要がある。
外部キーを設定することで
上記の例のように親がいないデータの登録、子のいる親の削除などができないため依存関係のあるテーブル間のデータに対して整合性を担保できる。
デメリット
外部キーを設定できない。
何度か説明しましたが、commentテーブルの親テーブルが複数存在するため外部キーを設定することができない。
それにより、どちらの親テーブルに紐づいているのか、そもそも親のデータが存在しているのか、データの更新時に整合性が取れているのかなど懸念点が多い。
解決策
参照を逆にする
comic
| comic_id | name |
|---|---|
| 1 | エンジニアになろう１巻 |
| 2 | エンジニアになろう２巻 |
| 3 | エンジニアになろう３巻 |
magazine
| magazine_id | name |
|---|---|
| 1 | 週刊エンジニア７月号 |
| 2 | 週刊エンジニア８月号 |
| 3 | 週刊エンジニア９月号 |
comment
| comment_id | book_id | book_type | comment |
|---|---|---|---|
| 1 | 1 | comic | なかなか好調な滑り出し |
| 2 | 2 | comic | ２巻からこの盛り上がりはすごい！ |
| 3 | 3 | comic | どうしてこうなった.... |
| 4 | 1 | magazin | あの超大御所エンジニアがこんなことを..... |
| 5 | 2 | magazin | あの有名エンジニアがそんなことを..... |
| 6 | 3 | magazin | あのエンジニアがあんなことを..... |
こちらも外部キーを設定できないデメリットはある。
しかしORMを使用していて以下のようなパターンでは意識的に選択することもある。
- ポリモーフィック関連の子側が複数の案件で共通して使われるフレームワークで、親側が個々の案件のアプリケーションである場合
- ポリモーフィック関連の子側がパッケージ製品で、親側がパッケージ購入者のアプリケーションである場合
- 関連の子側のテーブルと親側のテーブルの作成時期が離れているときなどである。
上記のパターンに当てはまらない場合は使用は推奨されない。
交差テーブルの作成
comic
| comic_id | name |
|---|---|
| 1 | エンジニアになろう１巻 |
| 2 | エンジニアになろう２巻 |
| 3 | エンジニアになろう３巻 |
magazine
| magazine_id | name |
|---|---|
| 1 | 週刊エンジニア７月号 |
| 2 | 週刊エンジニア８月号 |
| 3 | 週刊エンジニア９月号 |
comicComment
| comic_id | comment_id |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
magazineComment
| magazine_id | comment_id |
|---|---|
| 1 | 4 |
| 2 | 5 |
| 3 | 6 |
comment
| comment_id | comment |
|---|---|
| 1 | なかなか好調な滑り出し |
| 2 | ２巻からこの盛り上がりはすごい！ |
| 3 | どうしてこうなった.... |
| 4 | あの超大御所エンジニアがこんなことを..... |
| 5 | あの有名エンジニアがそんなことを..... |
| 6 | あのエンジニアがあんなことを..... |
交差テーブルではcommentテーブルの外部キーに加え、各親テーブルにも外部キーが定義することができ、データの整合性を取ることができる。
しかし両方の交差テーブルから参照された場合対処できないというデメリットもある
共通の親テーブル作成
book
| book_id | comment_id |
|---|---|
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
comic
| book_id |
|---|
| 1 |
| 2 |
| 3 |
magazine
| book_id |
|---|
| 4 |
| 5 |
| 6 |
comment
| comment_id | comment |
|---|---|
| 1 | なかなか好調な滑り出し |
| 2 | ２巻からこの盛り上がりはすごい！ |
| 3 | どうしてこうなった.... |
| 4 | あの超大御所エンジニアがこんなことを..... |
| 5 | あの有名エンジニアがそんなことを..... |
| 6 | あのエンジニアがあんなことを..... |
comicテーブルとmagazineテーブルの主キーはそれぞれ外部キーとなっておりbookテーブルの主キーの値と同じになっているためcomicテーブルとmagazineテーブルを直接commentテーブルに接続できる。
しかしこちらも両方の交差テーブルから参照された場合対処できないというデメリットがある
その他
book
| book_id | book_type | name |
|---|---|---|
| 1 | comic | エンジニアになろう１巻 |
| 2 | comic | エンジニアになろう２巻 |
| 3 | comic | エンジニアになろう３巻 |
| 4 | magazine | 週刊エンジニア７月号 |
| 5 | magazine | 週刊エンジニア８月号 |
| 6 | magazine | 週刊エンジニア９月号 |
comment
| comment_id | comment |
|---|---|
| 1 | なかなか好調な滑り出し |
| 2 | ２巻からこの盛り上がりはすごい！ |
| 3 | どうしてこうなった.... |
| 4 | あの超大御所エンジニアがこんなことを..... |
| 5 | あの有名エンジニアがそんなことを..... |
| 6 | あのエンジニアがあんなことを..... |
親テーブル（comicやmagazine）のように動的に追加される可能性がある、そもそも複数の種類を格納する可能性がある場合は上記のような方法が一番シンプルではないかと思う。
もう一つの方法として以下のようなパターンも検討できる。
親テーブルごとに子のcommentテーブルを作成し外部キーを付与するという方法である。
comic
| comic_id | name |
|---|---|
| 1 | エンジニアになろう１巻 |
| 2 | エンジニアになろう２巻 |
| 3 | エンジニアになろう３巻 |
magazine
| magazine_id | name |
|---|---|
| 1 | 週刊エンジニア７月号 |
| 2 | 週刊エンジニア８月号 |
| 3 | 週刊エンジニア９月号 |
comicComment
| comic_id | comment |
|---|---|
| 1 | あの超大御所エンジニアがこんなことを..... |
| 2 | あの有名エンジニアがそんなことを..... |
| 3 | あのエンジニアがあんなことを..... |
magazineComment
| magazine_id | comment |
|---|---|
| 1 | あの超大御所エンジニアがこんなことを..... |
| 2 | あの有名エンジニアがそんなことを..... |
| 3 | あのエンジニアがあんなことを..... |
まとめ
設計の段階で参照元と参照先が１つであることが大切であると感じた。
やむを得ない場合であっても親を複数持つパターンは整合性の担保という意味でも推奨されない。
次回
マルチカラムアトリビュート
参考文献
