【SQL】エンティティ・アトリビュート・バリューがなぜ悪いのか｜SKILLDO

今回はエンティティ・アトリビュート・バリュー(EAV)についてなぜアンチパターンなのかを見ていきたいと思います。

エンティティ・アトリビュート・バリュー(EAV)とは
なにが悪いのか
まとめ

エンティティ・アトリビュート・バリュー(EAV)とは

EAVは可変属性をサポートする必要があるとき、凡庸的な属性を「行」に格納するパターンです。

CREATE TABLE Issues (
  issue_id SERIAL PRIVARY KEY
);
INSERT INTO Issues (issue_id) VALUES (1234);

CREATE TABLE IssueAttributes (
  issue_id BIGINT UNSIGNED NOT NULL,
  attr_name VARCHAR(100) NOT NULL,
  attr_value VARCHAR(100),
  PRIMARY KEY (issue_id, attr_name),
  FOREIGN KEY (issue_id, REFERENCES Issue_id)
);

INSERT INTO IssueAttributes (issue_id, attr_name, attr_value)
  VALUES
    (1234, 'product', '1'),
    (1234, 'date_reported', '2009-06-01'),
    (1234, 'status', 'NEW'),
    (1234, 'description', '保存処理に失敗する');

このような設計はオープンスキーマ、スキーマレス、名前/値ペアとも呼ばれます。
利点としては下記が挙げられ、一見すると良い設計に聞こえます。

テーブルの列数を減らせる
新たな属性の定義にテーブルの列数を増やす必要がない
NULLだらけのテーブルになるのを防げる

なにが悪いのか

ここからはEAVの欠点を挙げていきます。

必須属性を設定できない

例えばdate_reported属性を必須属性にしたいとします。データベースの機能としてNOT NULL制約をかけるだけでその列を必須にすることができますが、EAVの設計ではそれができません。

型の指定ができない

例えばdate_repotedカラムに関係のない文字列’hoge’が入力されたらどうなるでしょうか。EAV設計の列のデータ型はあらゆる文字に対応するためにVARCHARで定義します。そのため、本来はじかなくてはいけない型のデータを許容してしまうことになります。

参照整合性を保証できない

データベースの従来の機能では外部キーを設定することによって、定義した属性の値を強制することができますが、EAVの設計では外部キーの設定を行うことができず、不正なデータの混入を許してしまいデータベースの中身を汚してしまう危険性があります。

属性名を補わなければならない

属性名の文字列に一貫性がない場合、それらすべてをサポートするクエリを書かないといけません。

SELECT date_reported, COUNT(*) AS bugs_per_date
FROM (SELECT DISTINCT issue_id, attr_value AS date_reported
  FROM IssueAttributes
  WHERE attr_name IN ('date_reported', 'report_date'))
GROUP BY date_reported;