読者です 読者をやめる 読者になる 読者になる

blog.toxn

あしあと

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