SQLアンチパターンのメモ1
第Ⅰ部データベース論理設計のアンチパターンのメモ
ジェイウォーク
- リストをカンマ区切りの文字列としてDBに格納すること
属性が複数の値を取る場合のデータの扱い方
- 交差テーブルを作成する
非正規形のテーブルは扱いにくいので、多対多の関係が生じるならば
交差テーブルを作成してその関係性を保存すると良い。
ナイーブツリー
- 階層構造をDBに格納する
掲示板のツリー型コメントのような階層構造をどのように保存するか。
1. 経路列挙型
各コメントについて、根からそのコメント(葉)までのパスを全て文字列として保存する
- パターン比較による葉の取得が容易
- パスの正確性が不明（検証するアプリケーションコードが必要）
- 長さの制限がある
2. 入れ子集合
直近の親だけでなく子孫の集合に関する情報を保存する
コメントの木を二分木として捉えて根からの深さ優先探索で辿りつける順番を求め、
各コメントの左葉と右葉となるコメントの番号(順番)を保存すればよい
- 非葉ノードの削除が容易
- ノードの挿入や移動が複雑になる（再計算しなければならない）
3. 閉包テーブル
- 各コメントの先祖/子孫関係を全て外部テーブルに保存する
コメントツリーパスを表すテーブルを作成し、コメントのテーブルから外部キーとして参照する
- 基本的に操作が容易-- テーブルが多くなる分、データ容量を使う
IDリクワイアド
- 主キーとしてとりあえずID列を定義する
IDとして使えそうな列があるにも関わらず、とりあえずID列を定義するのは考えものらしい
- ORマッパーによってはid列を求めるものもあるので、必要なら使う
- id列の定義によって本来重複しない列で重複してしまう恐れがある（その列を主キーとして扱えば良い）
これはどうなんだろ…
とりあえずIDでもよさそうな気はする
キーレスエントリ
- 外部キー制約を使用しないこと
参照整合性をアプリケーションコードによって検証することは困難
「100万分の1の確率で生じることは次の火曜日に起きる」らしい
なので、外部キーを使いましょう
EAV（エンティティ・アトリビュート・バリュー）
- 柔軟なソフトウェア設計のために可変属性をサポートする際に属性を「行」に保存する
- エンティティ：親テーブルに対応する外部キー
- 属性：属性名が各行にはいっている
- 値：エンティティの属性の値
オープンスキーマ、スキーマレス、名前/値ペアと呼ばれることがある
従来のDB設計で得られるメリット（必須属性の設定、SQLのデータ型、参照整合性 など）を得られない
特にメリットが無いので使わないほうがいい
1. シングルテーブル継承
全てのタイプの属性を個別の列に格納し、関連する全てのサブタイプを1つのテーブルに格納した上で、その行がどのサブタイプに属するかを定義する列を用意する
サブタイプの数が多いと大変なことになりそう
- 属性とサブタイプの対応関係は自前で管理する必要がある
- サブタイプの数とサブタイプ固有の属性の数が少ないときはこれが良い
2. 具象テーブル継承
サブタイプごとにテーブルを作成する
- シングルテーブル継承と比べて、サブタイプに存在しない属性列を格納しなくて良い
- サブタイプをまたいだ検索を実行する頻度が少なければ有効
- 全てのサブタイプに共通する属性と、サブタイプ固有の属性を区別することが面倒
3. クラステーブル継承
テーブルをオブジェクト指向のクラスであるかのように扱う
全てのサブタイプに共通する属性を含む基底型のテーブルを作成し、サブタイプごとにテーブルを作成する
外部キー制約によって1対1の関連を強制する
- 全てのサブタイプにまたがる検索が容易
4. 半構造化データ
属性名と値をXMLやJSONにしてTEXTとして保存する
シリアライズLOBと呼ばれる
- 拡張性が極めて高い（行ごとに異なるサブタイプを作ることも可能）
- サブタイプの数が多い場合や追加する機会が多い場合に柔軟に対応できる
- 特定の属性にアクセスすることが困難（アプリケーションコードでパースして対応する必要がある）
ポリモーフィック関連
- 複数の親テーブルを参照する際に、参照する親テーブルを文字列で格納する
どの親テーブルを参照するかわからないので、外部キー制約はつけられない
先に子となるテーブルを作成し、親テーブルを後で作る場合などでは使うかも（ポリモーフィック関連サポート機能のあるORMを使ったほうがいい）
参照を逆にすると解決できる
1. 交差テーブルの作成
子テーブルから複数の親テーブルは参照できないので、複数の外部キーを子テーブルを参照するために使用する
複数の親テーブルに対応する交差テーブルを作成し、各交差テーブルから子テーブルへの外部キーと各親テーブルへの外部キーを定義する
- メタデータがデータの整合性を保証してくれるため、間違いが起こりにくい
- UNIQUE制約をうまく使うと不要な関連が生じにくい
2. 共通の親テーブルの作成
全ての親テーブルが継承する基底テーブルを作成し、各親テーブルの主キーは基底テーブルを外部キーとする
- 外部キーによってデータの整合性が保証される
- 親テーブルと子テーブルをJOINしたクエリが容易に書ける
マルチカラムアトリビュート
- 1つのテーブルに属すべきだと思える属性に複数の値がある場合に、各属性を列として定義する
値の検索や更新が面倒になり、一意性が保証されない
属性が増加した場合にテーブル構造を変更することになり、様々な問題が発生しうる
- 従属テーブルを作成する
ジェイウォークと同様の解決策で、属性を格納する列を1つ持つ従属テーブルを作成する
従属テーブルで外部キーを定義して親テーブルの行に値を関連付ける
メタデータトリブル（メタデータ大増殖）
- スケーラビリティを高めるために、行数の多いテーブルを分割する
テーブルの増殖、データの整合性管理・同期が困難になる、テーブルをまたいだクエリ実行コードのメンテナンスが必要など、様々な問題が発生する
過去のデータを分離するような場合には使える
1. 水平パーティショニング
テーブル作成時に特定列の値によって行を分割し、パーティションを作成できる
- 物理的に分割されているテーブルに対して1つのテーブルであるかのように扱える
- SQL標準ではないが、主要なDBではサポートされている
2. 垂直パーティショニング
列でテーブルを分割し、パーティションを作成
一部の列をサイズが大きかったり、めったに使用されない列がある場合に有効
BLOB列やTEXT列などをデータベースで保存する際に、そういった列を別テーブルで保存するように設計する
- 固定長データと可変長データでテーブルをわけるとクエリが効率的に実行されるようになる
3. 従属テーブルの作成
マルチカラムアトリビュートと同様の解決策
