「SQLアンチパターン」を読んで【第Ⅰ部】
書籍
- 作者:Bill Karwin
- 出版社/メーカー: オライリージャパン
- 発売日: 2013/01/26
- メディア: 大型本
はじめに
読んだ書籍は1記事にまとめると文量が多くなるため、導入＋各部ごとの記事としてまとめる。
本記事は「第Ⅰ部：データベース論理設計のアンチパターン」になる。その他の部についての記事は下記参照。
- 導入
- 第Ⅰ部：データベース論理設計のアンチパターン(本記事です。)
- 第Ⅱ部：データベース物理設計のアンチパターン
- 第Ⅲ部：クエリのアンチパターン
- 第Ⅳ部：アプリケーション開発のアンチパターン
印象に残った点
※ 下記のカッコ書きは該当する章番号。
ジェイウォーク(信号無視)[1]
- 目的:複数の値を持つ属性を格納する - アンチパターン:カンマ区切りフォーマットリストを格納する - 検索時にパターンマッチが必要になりインデックスが効かない - アカウント更新/削除が辛い - アカウントIDの妥当性検証ができない - 区切り文字の選択が難しい - リストの長さに制限があり設計根拠に欠ける - アンチパターンを用いてもよい場合 - 非正規化によるパフォーマンス向上 - カンマ区切りのデータが必要 - 解決策:交差テーブルを作成する
- 今携わっている製品でカンマ区切りでデータを入れている箇所がある。アンチパターンのツライ点を見ても思い当たりそうなのは内容の妥当性検証くらい。メリットはテーブルを減らすことだろう。
- 設計した方はメリットデメリットを考えてこの方法を選んだのかもしれない。
ナイーブツリー(素朴な木)[2]
- 目的:階層構造を格納&クエリを実行する - アンチパターン:常に親のみに依存する(隣接リスト) - 階層を深くする度にJOINが必要 - ノード更新が非常に手間 - アンチパターンを用いてもよい場合 - DBMSで再帰クエリがある - 解決策:経路列挙/入れ子集合/閉包テーブル - メリットデメリットがあるので考慮して採用する
- 実務でツリー構造を取り扱ってはいないため、フムフム。そういう方法もあるのか。というコメントくらい。
- ツリー構造部分だけNoSQLでは駄目なのかな?と思い調べると同じ質問をされている方がおり、「RDBでもツリー構造は実装できるし、ツリー構造だけのためにNoSQLを採用するのはコストが高い。」と書かれていた。確かにそうだ。
IDリクワイアド(とりあえずID)[3]
- 目的:主キーの規約を確立する - アンチパターン:すべてのテーブルに「id」列を用いる - 冗長なキーが作成されてしまう(規約を重視して自然な主キーを無視する) - 重複行を許可してしまう(交差テーブルにidを用いる) - キーの意味がわかりにくくなる - すべて「id」にするとUSINGを使用できない - 複合キーは使いにくい - アンチパターンを用いてもよい場合 - ORMフレームワークの規約に従う - 疑似キーや主キーがあまりにも長くなる - 解決策:状況に応じて適切に調整する - idではなく分かりやすい名前にしたり、自然キーや複合キーを採用する
- 実務のテーブル設計ではidの名前には気をつけていると思う。ただ、自然キーを用いれるところもあると思うがidにした経緯が分からないところもある。
- 実務のシステムでは採番テーブルを使いシーケンス番号を振っているが明らかに無駄。DBMSにて提供されている機能を使えば良いのにと思う。
キーレスエントリー(外部キー嫌い)[4]
- 目的:データベースのアーキテクチャを単純化する
- アンチパターン:外部キー制約を使用しない
- 完璧なコードが前提となる
- 参照性の不整合ミスを調べなければならない
- アンチパターンを用いてもよい場合
- 外部キー制約をサポートしていないDBMSを利用している
- 極端に柔軟なデータベース設計を扱わないといけない
- 参照整合性制約を使えないとその他のアンチパターンに陥る可能性が高い
- 解決策:外部キー制約を宣言する
- カスケード更新により複数テーブルの更新が可能になる
- 事前のselectやテーブルロックが不要になる
- 孤立したデータが生じない
- 保守している製品は外部キー制約が使えるにも関わらず使っていない。設計真意は不明だが、孤立したレコードができたり、ガチガチにロックしたり、事前selectしたりしている。
- DB設計によってアプリ側の苦労が減るのではと感じた。
EAV(エンティティ・アトリビュート・バリュー)[5]
- 目的:可変属性をサポートする - アンチパターン:汎用的な属性テーブルを使用する - 属性の取得が冗長になる - DBが持つ整合性が利用できない (必須の制約がもてない、データ型を使えない、参照整合性を強制できない) - 行の再構築が必要 - アンチパターンを用いてもよい場合 - RDBの長所を失うため正当化出来る理由は簡単にない - 非リレーショナルな管理が必要であればNoSQLを使うべき - 解決策:サブタイプのモデリングを行う - シングルテーブル継承:1つのテーブルで全て管理 - 具象テーブル継承:共有属性込でサブタイプごとに管理 - クラステーブル継承:サブタイプ固有属性+基底テーブルの外部キーで管理 - 半構造化データ:1つのフィールドにサブタイプ固有の値を半構造化データ入れて管理
- 保守している製品はEAVは使っていないが、フィールド名にcodeとdataを用意し、セットされるcodeに応じdataの意味が変わるようにデータを保存している。参照整合性の問題やフィールドを見ただけでは何のデータか分からないという問題がある。
- うーん、RDBの良さを使っていないのは間違いない。
ポリモーフィック関連[6]
- 目的:複数の親テーブルを参照する - アンチパターン:二重目的の外部キーを使用する - ポリモーフィック関連を定義する(メタデータの混入) - アンチパターンを用いてもよい場合 - ORMフレームワークを用いている - 解決策:関連(リレーションシップ)を単純化する - 参照を逆にする - 交差テーブルを作成する - 共通の親テーブルを作成する
マルチカラムアトリビュート(複数列属性)[7]
- 目的:複数の値を持つ属性を格納する - アンチパターン:複数の列を定義する - 値の検索/追加/削除が辛い - 一意性の保証が出来ない - フィールド追加の影響が大きい - アンチパターンを用いてもよい場合 - 列の順番に意味を持たせる - 解決策:従属テーブルを作成する
- 同じ意味を持つ値を格納するために複数の列を定義すべきではないことは分かった。
- 汎用的な列を用意し、行ごとに列の使い方が異なるのはどうなんだろうか?この話とはちょっと意味合いが違うから、悪い設計ではないのか?デメリットはフィールド名が分かり辛いことぐらい?
メタデータドリブル(メタデータ大増殖)[8]
- 目的:スケーラビリティを高める - アンチパターン:テーブルや列をコピーする - テーブル/列が増殖する(年毎にテーブルが出来る) - データの整合性管理が必要になる - テーブルが分割されると外部キーを使えない - アンチパターンを用いてもよい場合 - 現在と過去のデータを合わせてクエリ実行をする必要がない場合 - 解決策:パーティショニングと正規化を行う - 水平パーティショニングの使用 - 垂直パーティショニングの使用 - 従属テーブルの導入
- 主要なDBMSはパーティショニングできるのか…。使ってみたい。
- 保守している製品は15年くらいのデータが蓄積されている場合もあるので、必要になってくると思う。
