参考書を読んだメモ : SQLアンチパターン I部1章
SQLアンチパターン
データベース論理設計のアンチパターン
ジェイウォーク（信号無視）
- 多対多の交差テーブルを避けるためにカンマ区切のリストを使うアンチパターン
目的: 複数の値を持つ属性を格納する
- 列に値を一つのみ格納する場合は簡単。整数、日付、文字列に対応した型を選択し、値がその型の1つのインスタンスであることを表現する
- あるアカウントは複数の製品を持つ場合があり、ある製品はただ一つの連絡先を参照しているデータベースがある
- 製品とアカウントの間には多対多の関係がある
- 製品が複数の連絡先を持つ場合があることがわかり、製品からアカウントに対する1対多の関連をサポートする必要ができた
- 複数の連絡先を持つにはどうするか
アンチパターン: カンマ区切りフォーマットのリストを格納する
- データベースの構造に対する変更を最小限に抑えるためにアカウントid列をVARCHAR型の列で再定義しカンマ区切のリストとして持つようにした
- この変更は一見テーブルや列を新たに追加せず、変更は1つの列のデータ型のみなのでうまくいってるように見える
特定のアカウントに関連する製品の検索
- 全ての外部キーが文字列連結されて1つのフィールドに格納されていると、クエリを作ることが難しくなる
- 島嘉靖による比較ができなくなるため、パターンマッチが必要になる
- インデックスを使うメリットも得られなくなる
- パターンマッチ構文は、各種データベース製品において異なるため、SQLはベンダー中立でなくなる
特定の製品に関連するアカウントの検索
- カンマ区切のリストを使って参照先のテーブルと結合するのは手間がかかる
集約クエリの作成
- 集約クエリはカンマ区切リストではなく複数行に対して使われるように設計されているため細工を行う必要がある
特定の製品に関連するアカウントの更新
- リストの追加は簡単だが、ソート順の維持が難しくなり、削除処理も複雑になる
アカウントIDの妥当性検証
- VARCHAR型の列のため正数値でなく文字列も入れこめてしまう
区切り文字の選択
- 整数値の区切りリストではなく、文字列の区切りリストであった場合区切り文字の選択や区別にコストがかかるようになる
リストの長さの制限
- 要素の長さによってリストに格納できる制限が変わってしまう
アンチパターンの見つけ方
- このリストでサポートしなくてはならない最大のエントリ数は?
- VARCHEAR列の最大値を決定しようとしている時に生じる疑問
- SQLで単語境界を一致させる方法を知ってる?
- 正規表現で文字列の一部を取得しようとしている
- リストのエントリに絶対使われない文字って何だっけ?
- 区切り文字を探している時に出てくる文言
アンチパターンを用いてもよい場合
-クエリパフォーマンスの向上のために用いる場合がある - カンマ区切りフォーマットのデータが必要で、かつリスト内の各要素への個別アクセスが不要な場合など - ただし非正規化を採用するには十分に検討してからの方が良い
解決策: 交差テーブルを作る
- アカウントIDを製品テーブルに格納するのではなく、新たに作成したテーブルの各行にアカウントidを一つずつ格納する
- このテーブルによって、製品とアカウントは多対多の関係になる
- テーブルが二つのテーブルを参照する外部キーを持つとき、そのテーブルは交差テーブルと呼ばれる
特定のアカウントに関連する製品の検索/特定の製品に関連するアカウントの検索
- 特定のアカウントに関連する製品の検索は交差テーブルを結合することで簡単にできる
- 結合はクエリパフォーマンスを低下させるが、アンチパターンの解決策よりも効果的にインデックスを使える
集約クエリの作成
- 単純なクエリで扱える
製品の連絡先の更新
- 交差テーブルへの挿入や削除で簡単に行える
アカウントIDの妥当性検証
- 外部キーを用いて別テーブルの値を参照することで入力の妥当性検証を行える
- 参照整合性を保証させることができる
- 外部キー制約により、存在するアカウントのみが格納できる
- データ型によって入力制限ができる
区切り文字の選択
- 区切り文字は不要
リストの長さの制限
- テーブルに物理的に格納できる行数のみ
交差テーブルの他のメリット
- インデックスを用いることで、リストの部分文字列とマッチさせるよりパフォーマンスは向上する
- 列を加えることにより、エントリに属性を追加できる
