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の扱い方をちゃんと意識するというのを守れていればーですけど。

blog.toxn

あしあと