SQLアンチパターン
1~8章の論理設計までで自分には十分そうだったのでそこまでを後で見返せるようにピックアップしてメモ。
1章 ジェイウォーク
アンチパターン
集合データを1カラムにカンマとかで詰め込むやつ
user_idsカラムにユーザーIDを1,2,3,4,5のようなVARCHARで挿入するような感じ
なぜダメか
idから先のレコードをjoinで取ってきたり集計も難しい
参照整合性も取れない。1,2,bananaのような文字でも入れられてしまう。
使っていい時
パフォーマンス目的の非正規化時。
先のレコードを参照することが不要な場合。
代替方法
交差テーブルの作成。has_manyの中間テーブル。
2章 ナイーブツリー
アンチパターン
コメントにコメントをつけられるようなツリーのデータ構造に対して、直近の親のみに依存したテーブルを作る。隣接リスト。
なぜダメか
ツリーの更新が大変。
全ての子孫の取得をするために配下のノード分だけクエリを投げないといけない
使っていい時
単純な親子の関係だけで事足りる場合。
列の挿入が楽ではあるのでそれを優先したい時。
代替方法
再帰クエリ
これが使えればベスト。MySQLも8.0から対応された。
経路列挙
pathカラムに1/2/3/4/のようなそのノードまでのパスを文字列で格納する
ただしある種ジェイウォークみたいなもんで参照整合性は維持できない
また、ネストの深さは有限(文字列の数に限度があるので)
閉包テーブル
ツリー全体のpathを格納するテーブルを別途用意するやつ。再帰クエリが使えないならこれ。
3章 IDリクワイアド
アンチパターン
とりあえず主キーとしてID列を作ること
行の重複を避けたい/クエリで個別の行を参照したい/外部キー参照をサポートしたい
なぜダメか
ID以外の復号キー(例えばbug_id,product_idでユニーク)なキーが必要な場合、IDが無駄
IDというキー名が抽象的すぎ
外部主キーがそもそも取り扱いがやや面倒。参照するときは特に。
使っていい時
ActiveRecordのような規約で縛りがある場合
キー名が長くなりすぎる場合(ファイルシステムのpath名はユニークになるが長すぎ)
代替方法
本当にID列が必要か立ち止まる
わかりやすい命名にする
命名規則についてはISO/IEC 11179を読むと良い。この規格のSQL部分のみに焦点を当てた本がある。
4章 キーレスエントリ
アンチパターン
外部キー制約を使用しない
なぜダメか
参照整合性を保証するためのコードを開発者側で書かないといけない。完璧な開発者によるコードが求められる。 データ不整合に陥ったレコードを特定するための無駄な作業などが発生する
使っていい時
外部キー制約を使えないDBを使う時
代替方法
外部キー制約を使う
5章 EAV
アンチパターン
汎用的な属性テーブルを使用する
こんな感じ。本来は行のカラムと値であるはずのstatusやpriorityカラムがattr_nameの値になっている。これで親テーブルに対して可変で属性を追加できる。
table:Attributesテーブル
issue_id attr_name attr_value
1234 status new
1234 description ホゲホゲ
1234 priotity HIGH
なぜダメか
attr_nameに入る列名の文字列になんでも入力可能になる。
データ整合性が保てない
attr_valueに対して参照整合性が強制できない
SQLのデータ型が使えない
行を再構築しないといけない
使っていい時
使っていいときはほぼない。非リレーショナルなDBを使うことも検討した方がマシ
代替方法
サブタイプのモデリングを使う
全ての属性を１つのテーブルに格納する
具象テーブル継承
属性ごとに別々のテーブルを作る。
bugs
issue_id
name
severity
feature_requests
issue_id
name
sponsor
クラステーブル継承
親テーブルへの外部キーと親テーブルに追加したい属性のみを具象クラスのようにそれぞれ別テーブルにする
issues
id
name
bugs
severity
feature_requests
sponsor
半構造化データ
サブタイプの数が多い場合はjsonやxmlをそのままカラムに突っ込む
6章 ポリモーフィック関連
アンチパターン
ポリモーフィック関連。二重目的の外部キーを使用すること。
なぜダメか
外部キー制約が使えないので参照整合性が保てない。外部キー制約はテーブルを一つに限定するため。
使っていい時
参照整合性が保てないので基本は使わない。使ってもいい場合はORMやその他他社製品との都合でそもそも仕様として参照整合性が保てない(テーブルの更新が同期的ではない場合)などのみ。
代替方法
参照を逆にする
has_manyな交差テーブルを作成する
交差テーブルのxxx_idにユニーク制約をつけることでhas_onの強制もできる
共通の親テーブルの作成
5章のクラステーブル継承と同じ構造にする
7章 マルチカラムアトリビュート
アンチパターン
同じ性質の複数の列を定義する
bugs
bug_id
tag1
tag2
tag3
なぜダメか
基本は1章と同じ。
検索がしづらい・追加/削除の煩雑さ・一意性を保証できない・列がどんどん増えていくなど
使っていい時
属性の選択肢が限定される場合
代替方法
従属テーブルを作成する。
bugs
id
tags
bug_id
name
8章 メタデータトリブル(メタデータの大増殖)
アンチパターン
テーブルや列をコピーする
例えば毎年revenue2001,revenue2002,revenue2003というテーブルを作るようなやつ
なぜダメか
EAVやポリモーフィックはメタデータをデータに混入してるが、これはテーブルやメタデータにデータを混入させてしまっている
テーブルが増殖する
テーブルに入れるデータの整合性チェックが抜けがち。CHECK制約をかけ忘れないようにしないといけない。
データの同期が難しい。BigQueryならよくやるパターンだけど。
その他たくさん
使っていい時
アーカイブ目的。過去データを最新のデータから分離するようなパターン。
代替方法
パーティショニングと正規化をする
シャーディング
列ごとに分割
従属テーブルの導入
章
アンチパターン
なぜダメか
使っていい時
代替方法