はじめに
- この記事はSQLアンチパターンを読んだ私的まとめです
- 今更ながらDBを扱う上での入門ということで読んでみています
- 本文にはここ以上に詳しく、データベースの例が示されながら解説があリます
I部:データベース論理設計のアンチパターン
１章：ジェイルウォーク（信号無視）
アンチパターン
- 1つのカラムに区切り文字で複数の値を入れる
- SQLを発行するときに正規表現が必要になる
- Indexが張れないのでパフォーマンスが出ない
解決策
- 交差テーブルを作成する
- JoinすることでIndexが使える？
- 外部キー制約が貼れる
- 上記の副作用は解消される
所感
基本このパターンにならないようにしないといけない
DBやアプリケーションがそのカラムの中身に興味がない場合は、JSONなどを格納するのはある
２章：ナイーブツリー（素朴な木）
アンチパターン
- 隣接リスト
- 常に親にのみ依存する
- 深い階層構造の時のクエリが複雑に
- 階層の深さを考える必要がある
- 依存関係が強く削除し辛い（親に依存した子がばかり）
対処パターン
- 隣接リスト
- 深くならないことがわかってればまあ採用してもOK
- 再起クエリ
- 再帰的に使用できるクエリ宣言をサポートされてたらそこまで隣接リストも悪くない
- 経路列挙
- カラムにPathを記述し、そのPathに対して検索をかける
- ジェイルウォークのアンチパターンにハマる可能性がある
- 入れ子集合
- ツリーに関する集合情報をカラムに保存する
- ツリー検索が多い場合は有用
- 削除や更新の際に全テーブルデータを更新かけなければならないことがある
- 閉包テーブル
- 関連付けを別テーブルで管理する
- 階層が深くなると必要行数が多くなる
所感
閉塞テーブル一択か？
隣接リストをそのまま使えるような設計をすべきかもと感じた
3章：IDリクワイアド（とりあえずID）
アンチパターン
- 何も考えずにid列を追加する
- 他の列にもIDがあり、それらでUniqueであればidは冗長
- idだけでは何のIDかわかりにくい
- 重複データが生まれる可能性がある（UNIQUE制約が必要）
解決策
- 状況に応じて適切に
- 主キーは制約であり、データ型ではない
- わかりやすい名前をつける
- ORMを活用するならまあつけてもいいかも
所感
テーブル分割を考えるとあった方が良い気がする
ただ無駄なIDになっているのは確か
重複登録に関しては正しいUNIQUEKEYを張る方が先ではないだろうか？
４章：キーレスエントリ（外部キー嫌い）
アンチパターン
- 外部キー制約を使わない
- 存在し得ないレコードが生まれる
- ミスをしない前提になっている
- ミスを発見しなければならない
- ミスを訂正しなければならない
解決策
- 外部キー制約を使う
- 使え
- それ以外にない
所感
使え、それ以外にない。
ないことによるいいことはない。人間はミスする
５章：EAV（エンティティ・アトリビュート・バリュー）
例
-
EAVとは別テーブルにあるテーブルレコードの属性情報を持たせること
- 主テーブルはIDだけをもち、別テーブルにそのIDに紐づけて、日付、属性、種類などを別々のレコードとして格納する
- 下記に例を示す
Issueテーブル
| issue_id | comment |
|---|---|
| 1 | hoge |
| 2 | fuga |
| 3 | piyo |
- IssueAttrubuteテーブル
| issue_id | attrbute_name | attribute_value |
|---|---|---|
| 1 | product | 1 |
| 1 | date_reported | 2009-06-01 |
| 1 | status | NEW |
| 1 | priority | HIGH |
| --- | --- | --- |
| 2 | product | 1 |
| --- | --- | --- |
| 3 | product | 1 |
アンチパターン
- 上記のような汎用的な属性テーブルを使用する
- 必須属性が設定できない（ある属性をNotNullに指定できない）
- SQLのデータ型を使えない→ dateではなくstringになったりする
- 参照整合性を強制できない→全てのレコードに適応されてしまうため
- 属性名を補わなければならない
- 取得の際に行の再構築が必要
解決策
- シングルテーブル継承
- 別テーブルに分けず、全て１テーブルに格納する
- NULL行が多くなるデメリット
- 新しい属性が必要になったらカラムを追加する必要がある
- 具象テーブル継承
- 全ての属性ごとに別々にテーブルを作成する
- NULL行が不要になる
- 全ての属性を跨いだ検索の実行する頻度が少なければ有用
- クラステーブル継承
- 共通の基底テーブルを作成する
- 属性ごとに追加のテーブルを作成し、基底型テーブルに対する外部きーの役割を持つ主キーを設定
- 半構造化データ
- ある行に対してJSONなどの形式で属性情報を保存する
- 属性の追加が多い際に有用
- ただしJSONなどを保存するのはそれはそれでアンチパターン
所感
普通に属性が多くないのであればシングルテーブル継承で十分な気がした。
が、Null行があること自体はあまり好ましくないのでできるだけ具象テーブル継承を行うべきか。
構造化データを入れるのはアンチパターンとならない時だけにすべきかと
6章：ポリモーフィック関連
アンチパターン
- 二重目的の外部キーを使用する
- あるテーブルのカラムの内容によって、参照される外部テーブルが異なる
- 外部キー制約をかけれなくなる
解決策
- 関連を単純化する
- 参照を逆にする
- 逆の参照関係にすれば上記のようなことは起こらない
- 交差テーブルを作成する
- それぞれに別の交差テーブルを作成し、そこから元テーブルを参照する
- 共通の親テーブルを作成する
- 親テーブルが関連づけ扱うことで上記のようなことはなくなる
- 参照を逆にする
所感
この構造の時のパターンを実際に確認してないのでよくわからんかった
どのテーブルを参照するのか？をカラムで指定していることを発見したら気をつけよう
7章：マルチカラムアトリビュート（複数列属性）
同じ属性だが複数の値を登録したい時
アンチパターン
- 複数の列を登録する
- 入れれる場所を増やしただけで、値の検索を複数列に対して行う必要がある
- また追加と削除も全ての列に対して行う必要がある
- 複数列では一意性の保証ができない
- 単純に列が増えることに処理すべき対象がどんどん増える
解決策
- 従属テーブルを作成する
- 別のテーブルを作成し、属性値を複数行で格納する
- 従属テーブルに外部キーを定義し、親のテーブル行に値を関連づける
所感
解決策が明快、わかりやすい。
同じような属性を列で格納しようとした時に注意しよう
8章：メタデータトリブル(メタデータ大増殖)
データ容量が増えてきた時に陥るアンチパターン
アンチパターン
- 行数の多いテーブルを複数テーブルに分割する
- どの行がどのテーブルに属するか定義するポリシーが必要になる
- データの整合性の管理が必要
- 定義の更新もれなどで新しいデータオブジェクトが作成されてなければエラーに
- データの同期
- 別々のテーブルに入っていると更新をかけた際に不整合が発生することもある
- 一意性の保証
- 複数のテーブル間で主キーが一意であることを保証する必要がある
- テーブルをまたいだクエリ実行
解決策
- 水平パーティショニングの使用
- シャーディング
- SQLのパーティション機能で分けたい対象を決めて分割する
- 使う側はあたかも1つのテーブルであるかのように扱える
- 垂直パーティショニングの使用
- 従属テーブルの導入
- 分けたい対象を別テーブルとして定義する
所感
データが多くなることの考慮が必要か
水平パーティショニングが一番手っ取り早そう
I部完
- データベースの論理設計のアンチパターンをみてきた
- 最初に設計する際にこれらを避けて行きたい
- 時に最初のジェイルウォークには現在進行形で悩まされてるので、今後はこんなことないように。。。
- 次はII部 データベース物理設計のアンチパターン を進めていく予定
- 次の週に出せたらいいな。。。