会社の輪読会でSQLアンチパターンの4章、5章を読んだのでそのまとめ。
- 作者:Bill Karwin
- 発売日: 2013/01/26
- メディア: 大型本
4章 キーレスエントリ（外部キー嫌い）
先に結論 - 外部キーつけろ！！！
アンチパターン：外部キーを使用しない
外部キー制約を省略するとDB設計がシンプルになり、柔軟性が高まり、実行速度が早くなると思ってる人もいるかも知れないが、そこには代償があり代償は別の形で支払う必要がある。
外部キーを使用しない場合の大変さ
完璧なコードを前提にする必要がある
- テーブルの関連付けを常に維持するコードを書くこと
- 行の挿入時には外部キー列の値が参照先のテーブルの既存の値を参照してることを確認すること
- 行の削除は関連する子テーブルも適切に更新されること
つまるところ「ミスをしないようにすること」
ミスを調べないといけない
- 破損したデータを調べるスクリプトをたくさん作成する必要がある。似たようなクエリをすべての参照に対して書かないといけない
- チェックする頻度も検討しないといけない
すべてのアプリケーションからのアクセスを把握する
外部キーを書きたくない理由
外部キーを書きたくない理由は、複数のテーブルの関連し合う列を更新するときに外部キー制約が邪魔になるから(キャッチ =22)。
アンチパターンを利用していい場合
外部キーをサポートしてないDBを使う場合
４章まとめ
外部キーつかえ！！ (最初に書いたけど)
5章 EAV
(エンティティ・アトリビュート・バリュー)
可変項目のサポート
オブジェクト指向をもとにしたテーブル設計をする場合に、基底クラスでサブクラスでテーブルを分けるとする。
その場合、サブクラスに対応するテーブル毎に異なる項目を設定する必要がある。 そんなとき、汎用的な属性テーブルがあれば解決すると考えてしまう。 (これがアンチパターンのはじまり)
EAVの例 Issueテーブル
| issue_id |
|---|
| 1234 |
IssueAttributesテーブル
| issue_id | attr_name | attr_value |
|---|---|---|
| 1234 | product | 1 |
| 1234 | data_reported | 2009-06-01 |
| 1234 | status | NEW |
| 1234 | description | 保存処理に失敗する |
EAV
名前/値ペアともよばれる。
メリットは - 両方のテーブルの列数を減らせる - 新しい属性が増えても列数を増やさなくていい - 属性が存在しないエンティティの該当列にNULLが入ってるNULLだらけのテーブルになるのを防げる。（使わない項目が多すぎるテーブルとか）
EAVのデメリット
- テーブルから報告日の項目を取得したい場合、クエリが冗長になる。
- 必須属性を設定できない
- SQLのデータ型を使えない。
- 参照整合性を強制できない (外部キーをつけるとすべての行に適用されてしまう)
- 属性名補わないといけない (attr_nameに入る名前がバラバラになる可能ある)
- 行を再構築しないといけない
クエリで各属性の行をJOINする必要があるし、クエリ作成時には属性の名前をすべて指定する必要がある。
| issue_id | data_reported | status | priority | description |
|---|---|---|---|---|
| 1234 | 2009-06-01 | NEW | HIGHT | 保存処理に失敗する |
アンチパターンを使用していい場合
RDBでEAVを使う理由は簡単に見つからない。 RDBの長所が失われるから。 非リレーショナルなデータ管理が必要なら非リレーショナルな技術を使うべき。 （dynamoDB、Cloud DataStore、Cassandra、MongoDB、Redisとか）
EAVを使わずにEAVが扱うようなデータを格納する方法
シングルテーブル継承
すべてのタイプの属性を個別の列に格納して、関連するすべてのサブタイプを１つのテーブルにまとめる。
デメリット
- NULLと非NULLの列がバラバラに点在してしまう。
- 新しいオブジェクトタイプが増えるとその分の属性が増えるので列数の実際的な上限に達する可能性もある。
具象テーブル継承
サブタイプ毎にテーブルを作成する。 （基底の属性 + サブ固有属性のテーブル） サブタイプに存在しない属性列を格納する必要がないことがメリット
デメリット
- すべてのサブタイプに共通する属性とサブタイプ固有の属性の区別が簡単につかない
- 共通属性に新しい属性を追加する場合、すべてのサブタイプの属性の変更が必要になる。
- すべてのオブジェクトを取得したいときもテーブルがわかれるので面倒
クラステーブル継承
オブジェクト指向の継承を模倣する、 基底型のテーブルを１つ作り、サブタイプ毎にテーブルをつくる。 サブタイプテーブルは基底型テーブルに対する外部キーの役割を持つ主キーを設定する。 基底型テーブルと１対１の関連が強制される。
半構造化データ
XMLやJSONなどの構造化されたデータをそのまま打ち込む （ジェイウォーク大丈夫？）
デメリット
- SQLが特定の属性にアクセスする手段を殆ど持ってない。
新しい属性を随時定義するための高い柔軟性が必要な場合に適している