SQLアンチパターンその５ EAV（エンティティ・アトリビュート・バリュー）
EAV（エンティティ・アトリビュート・バリュー）とは
Attribute（属性）とValue（値）のペアを１行として登録すること。
CREATE TABLE Products ( product_id BIGINT UNSIGNED NOT NULL, name VARCHAR(100), PRIMARY KEY (product_id) ); CREATE TABLE Product_attributes ( product_id BIGINT UNSIGNED NOT NULL, attribute_name VARCHAR(100) NOT NULL, attribute_value VARCHAR(100), PRIMARY KEY (product_id, attribute_name), FOREIGN KEY (product_id) REFERENCES Products(product_id) );
(product_id, name) ------------------ (1, "hogehoge") (product_id, attribute, value) ------------------------------ (1, "brand", "NB") (1, "country", "japan") (1, "custom1", "piyo")
いつ起こるのか
可変属性のサポート。テーブルの設計変更を柔軟にしようとする
何をしてはいけないのか（アンチパターン）
汎用的な属性（EAV）テーブルを作ること
何故ダメなのか
- 属性値の取得クエリの明確さが低下する。
-- 従来のやりかた SELECT product_id, brand FROM Products;
-- EAVでのやりかた SELECT product_id, attribute_value AS brand FROM Product_attributes WHERE attribute_name = 'brand';
- 適切なデータ型が使用できなくなる。
- NOT NULL制約、CHECK制約、FOREIGN KEY制約が使えなくなる。
可変な属性に対応すると、どんな値が入っても良い＝列ごとに設定するべき制約を設定できなくなる。
- 本来１行だった行を作り直すのが大変。
SELECT p.product_id, a1.attribute_value AS brand, a2.attribute_value AS country, a3.attribute_value AS custom1 FROM Products AS p LEFT OUTER JOIN Product_attributes AS a1 ON p.product_id = a1.product_id AND a1.attribute_name = 'brand' LEFT OUTER JOIN Product_attributes AS a2 ON p.product_id = a2.product_id AND a2.attribute_name = 'country' LEFT OUTER JOIN Product_attributes AS a3 ON p.product_id = a3.product_id AND a3.attribute_name = 'custom1' WHERE p.product_id = 1;
いい解決策は
- テーブル継承
- 準構造化データ
- アプリで頑張る
テーブル継承には以下の方法がある。いずれもOOPの継承の概念をテーブルで表現したもの。
- 単一テーブル継承（STI ... Single Table Inheritance）
- 具象テーブル継承
- クラステーブル継承
単一テーブル継承は、サブクラスのメンバ（列）をすべて一つのテーブルに含む方法。共通部（親クラスメンバ）を除き、行毎にタイプが異なるため、ある行で値を持たない列にはNULLが入る。
具象テーブル継承は、サブタイプ毎にテーブルを作成し、共通列もそれぞれで持つ方法。
クラステーブル継承は、サブタイプのメンバだけを持つテーブルを新たに作り、親テーブルからの参照を張る方法。これは垂直分割であり、テーブルの正規化をするときの方法。
準構造化データ JSONとかXML形式のデータをBLOB型の列として持つ。 アプリ側でパースして、内容のチェックが必要になるため、DBに頼らずアプリで頑張る方法。
アプリで頑張る フレームワークによってはEAVを使っている場合もあるし、使わざるを得ない場合もある。その時は型チェックとか諸々をアプリで頑張る。
NULLの話も後で出てくるんですが、新たな属性が必要になったときは列を追加する＝STIでいく のがよいかと。 ワイルドカードを使わない、NULLの扱い方をちゃんと意識するというのを守れていればーですけど。
SQLアンチパターンその４ キーレスエントリ（外部キー嫌い）
SQLアンチパターンは全部で25章あるので、毎日書いても１ヶ月かかる内容だった。 なので、今後はそんなにがっつり書かずにポイントだけ。
キーレスエントリ（外部キー嫌い）とは
外部キー制約を使わない
いつ起こるのか
テーブル間で参照をするとき
何をしてはいけないのか（アンチパターン）
外部キー制約を使わない
何故ダメなのか
外部キー制約は使われず、でも外部キーとして参照されている列がある場合、アプリケーションコード側で完璧にお守りをしてあげないと迷子になる行が出てくる。アプリが完全であることを前提にしてはいけない。
いい解決策は
外部キー制約使え
参照関係をそもそも壊さない仕組みだから、外部キー制約使うべき。
マスタになるキーを更新する際、ON DELETE CASCADE, ON UPDATE CASCADEとか使えば、外部キーとして参照しているテーブル側もちゃんと更新なり削除がかかる。 知らなかった。これ覚えておくべき。
外部キー使う場合、たしかにオーバーヘッドになり得るけれど、アプリケーションコード側で事前チェックとか気にする必要なくなるし、DB側でできる部分は無理にアプリケーションに持ってくること無い。
ファイル内の重複行削除
ファイル内の重複している行を削除する。
追記： kotyさんに教わった部分を入れてみて、FileUtility2.csとして投稿。かなりスッキリ
Enumerable.Distinct(TSource) メソッド (IEnumerable(TSource)) (System.Linq)
SQLアンチパターンその３ IDリクワイアド（とりあえずID）
IDリクワイアド（とりあえずID）とは
名前のまま、「とりあえずID列作っておこうぜ」ということ
いつ起こるのか
主キーを作るルールがあるとき
何をしてはいけないのか（アンチパターン）
どんなテーブルに対しても「ID」という名前の列（サロゲートキー）を作ること
例：店で販売する商品
CREATE TABLE Goods( id SERIAL PRIMARY KEY, product_id BIGINT, name NVARCHAR(20), ); (id, product_id, name) ---------------------------------- (1, 1014, "レッドブル エナジードリンク 缶185ml") (2, 1237, "ライジン 缶185ml") (3, 1935, "モンスターカオス 缶355ml") (4, 2084, "リゲイン エナジードリンク 缶190ml") (5, 2365, "バーン エナジードリンク 缶250ml") ...
何故ダメなのか
冗長な列ができてしまう
まさに上の例とかそうだけど、他の列を自然キーとして扱えるのならば、あえてid列を追加する必要は無い。
いい解決策は
「id」以外の名前をつける
「商品コード」とか、「user_id」みたいなわかりやすい名前を付けることを心がけること。交差テーブル作った時に、交差する両方のテーブルの主キーが「id」だと交差テーブルが扱いにくくなる。
自然キーで使えそうなものって、JANコードとかかなーと思ったんですが、再利用される可能性あるし、見極め難しそう。サロゲートキー使うでも良さそうだし、ケースバイケースだろうなと。
SQLアンチパターンその２ ナイーブツリー（素朴な木）
ナイーブツリー（素朴な木）とは
「素朴な」という訳がぴんとこなかった… 「単純、未熟な」（思慮から生まれる）ツリー構造。
いつ起こるのか
表の構造で、ツリー（階層）構造を格納しようとするとき
何をしてはいけないのか（アンチパターン）
階層として「親への参照だけ」を持つこと。
例：会社の組織図を表すテーブルを作る
CREATE TABLE Departments( department_id SERIAL PRIMARY KEY, parent_id INT, name NVARCHAR(20), FOREIGN KEY (parnt_id) REFERENCES Departments(department_id) ); (department_id, parent_id, name) ---------------------------------- (1, NULL, "本社") (2, 1, "A本部") (3, 1, "B本部") (4, 2, "A営業部") (5, 2, "A開発部") (6, 3, "B営業部") ...
何故ダメなのか
階層の探査をするクエリが非効率的。1階層下の子ノードの集合を取るのには、以下の様なクエリが必要。
SELECT d1.*, d2.*
FROM Departments d1
LEFT OUTER JOIN Departments d2
ON d2.parent_id = d1.department.id;
これがn階層になると、d1., d2., ... dn.* となり、もちろん階層分だけJOINすることになる。
中間のノードの削除を行う際に、親子関係を適切に組み替えるためのクエリも複雑になる。
いい解決策は
閉包テーブル（Closure Table）を作る
あるノードを取り上げた時、そのノードのすべての祖先と自分に対しての組み合わせを、行として格納する。 先ほど挙げたDepartmentsテーブルに対して考えると、以下のようになる。
CREATE TABLE TreePaths( ancestor INT NOT NULL, descendant INT NOT NULL, PRIMARY KEY (ancestor, descendant), FOREIGN KEY (ancestor) REFERENCE Departments(department_id), FOREIGN KEY (descendant) REFERENCE Departments(department_id) ); (ancestor, descendant) -------------------------- (1, 1) (1, 2) (1, 3) (2, 2) (2, 4) (2, 5) (3, 3) (3, 6) (4, 4) (5, 5) (6, 6)
閉包テーブルを作ると、A本部の部署を取得する場合、以下のようなクエリでいい。
SELECT d.* FROM Departments d INNER JOIN TreePaths As t ON d.department_id = t.ancestor WHERE t.descendant=3
中間のノードの削除を行う場合でも、全ての祖先への参照が格納されているため、消したいノードのdepartment_idの行だけ消せばいい。
SQLアンチパターンその１ ジェイウォーク（信号無視）
ジェイウォーク（信号無視）とは
（例えば）カンマ区切りで複数の値を持つ列を作ること。
例：アカウントのテーブル。このアカウントは複数のグループに所属できる。
(account_id, name, group_id) --------------------------- (1, "hoge", "1,2,36,103") (2, "moga", "2,4")
なぜアンチパターンなのか
区切り文字を気にしてアプリを実装する必要がある
区切り文字をカンマにした場合、区切り文字以外にカンマが出てくる可能性はゼロではない。（上の例だとgroup_idは数値だから大丈夫に見えるけど。）じゃあどんな区切り文字だったら大丈夫なのかは分からない。
列のデータ型が文字列になってしまう
group_idのデータ型がIntegerだとしたら130は入っても、"piyo"なんて値は入るはず無い（エラーする）けど、「カンマ区切りで記された数値」を保存する列（varchar）だった場合、"1,2,4"と、"1,2,piyo"はどちらもデータとしては間違っているが、データ型としては正しい。
列の最大サイズを決められない
いったい何個のgroup_idがカンマ区切りで入ってくるのか予想はできない。どれくらいのサイズを取っておけば安全なのか誰もわからない。
データの修正が複雑
文字列解析してgroup_idを追加・変更・削除するのはとても面倒。
どうするべきか
第一正規化して別テーブル（交差テーブル）を作る。
(1, "hoge", "1,2,36,103") (2, "moga", "2,4") ↓ (1, "hoge") (2, "moga") (1, 1) (1, 2) (1, 36) (1, 103) (2, 2) (2, 4)
文字列をsplitする必要もないし、データ型がおかしいデータも入らない。サイズに関しても予想がつく範囲内におさまるし、データの修正や削除もSQL文１行で出来る。
アンチパターンに限らない話だと思いますが、「アプリ側できちんと制御してやれば変なデータは入らない。」というのは「安全装置ついてない刃物むき出しの機械なんだけど、注意すれば事故は起こらない」って言ってるのと同じ。しなくてもいい注意は排除して、安全な設計でいきましょう…ということだと思います。
SQLアンチパターン読み始めた
仕事でDBを触る機会が増えました。 転職前はSQLもうろ覚えだったんですけど、割と慣れてきました。
いざ使いはじめると業務が持つデータ量の多さにビビります。 あとは、アプリ開発していく中で既存のテーブルをメンテナンスしたり、自分で必要なテーブル作るようになってきたので後で困らないような設計にしないとなーと思って、「SQLアンチパターン」読み始めました。
- 作者: Bill Karwin,和田卓人(監訳),和田省二(監訳),児島修
- 出版社/メーカー: オライリージャパン
- 発売日: 2013/01/26
- メディア: 大型本
- 購入: 9人 クリック: 698回
- この商品を含むブログ (40件) を見る
まだ第一部しか読んでませんが、割と思い当たるところがあったので頭の整理のためにもまとめていきます。 長くなりそうなので、記事分ける。。。
