SQLアンチパターンその5 EAV(エンティティ・アトリビュート・バリュー)
EAV(エンティティ・アトリビュート・バリュー)とは
Attribute(属性)とValue(値)のペアを1行として登録すること。
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制約が使えなくなる。
可変な属性に対応すると、どんな値が入っても良い=列ごとに設定するべき制約を設定できなくなる。
- 本来1行だった行を作り直すのが大変。
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の扱い方をちゃんと意識するというのを守れていればーですけど。