Edited at

5章 EAV (エンティティ・アトリビュート・バリュー)

More than 5 years have passed since last update.

日付ごとの桁数を計算するようなタスク


EAV/intro/count.sql

SELECT date_reported, COUNT(*)

FROM Bugs
GROUP BY date_reported;

このようなクエリには、2つの前提条件が必要


  • 値が同じ列に格納されていること


    • 例: Bugs.date_reported




  • GROUP BY で正確に日付をグループ化するために、値が比較できること



    • Bugs.date_reported のフォーマットが揃っている必要がある



EAV (エンティティ・アトリビュート・バリュー) と呼ばれるアンチパターンを用いていると、上記の前提条件が成立しない問題に遭遇する


  • 日付が行によって違う列に格納されている


    • 例: date_reported, report_date



  • 日付のフォーマットが異なり、簡単には比較できない


5.1 目的: 可変属性をサポートする

バグデータベースの例

Issue (問題) を基底型として、BugFeatureRequest (機能要望) のオブジェクトを管理する



  • Issue 基底型 - 共通の属性



    • Date_reported: 報告日時


    • Reporter: 報告者


    • Priority: 作業優先度


    • Status: Issue の状態




  • Bug 独自の属性



    • Severity: バグの重大度


    • Version_affected: バグが発生した製品のバージョン




  • FeatureRequest 独自の属性



    • Sponsor: 機能要望の開発に出資しているスポンサー



リレーショナルデータベースで高い拡張性を保ちながら上記のようなデータを管理するには?


5.2 アンチパターン: 汎用的な属性テーブルを使用する

もう1つの別のテーブル(属性テーブル)を作成し、属性を行に格納する

属性テーブルの3つの列


  • エンティティ


    • 親テーブルに対応する外部キー



  • 属性


    • 従来型のテーブルでは列の名前にあたる属性名




    • エンティティの属性の値




EAV/anti/create-eav-table.sql

CREATE TABLE Issues (

issue_id SERIAL PRIMARY KEY
);

INSERT INTO Issues (issue_id) VALUES (1234);

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

INSERT INTO IssueAttributes (issue_id, attr_name, attr_value)
VALUES
(1234, 'product', '1'),
(1234, 'date_reported', '2009-06-01'),
(1234, 'states', 'NEW'),
(1234, 'description', '保存処理に失敗する'),
(1234, 'reported_by', 'Bill'),
(1234, 'version_affected', '1.0'),
(1234, 'severity', '機能の損失'),
(1234, 'priority', 'HIGH');


このような設計をエンティティ・アトリビュート・バリュー (Entity-Attribute-Value)、略して「EAV」と呼ぶ

※ オープンスキーマ、スキーマレス・名前/値ペアと呼ばれることもある


  • テーブルの列数を減らせる

  • 新たな属性をサポートする際も、列数を増やす必要がない

  • 属性が存在しないエンティティに NULL が入るような、NULL だらけのテーブルになることを防げる

といった改善点があるように見えるが、実は設計の複雑さは解消できていない


5.2.1 属性を取得するにはどうするか

報告日別に全てのバグを検索

従来型のテーブル設計


EAV/anti/query-plain.sql

SELECT issue_id, date_reported FROM Issues;


EAV を利用した設計の上のクエリ


EAV/anti/query-eav.sql

SELECT issue_id, attr_value AS date_reported

FROM IssueAttributes
WHERE attr_name = 'date_reported';

クエリが冗長になり、明確さも低下している


5.2.2 データ整合性をどう保つか

EAV によって、従来型のデータベース設計で得られる利点を失ってしまう


必須属性を設定できない

date_reported 属性に値が入っていることを必須にしたい


  • 従来型のデータベース設計



    • NOT NULL 制約を宣言すればよい



  • EAV 設計



    • Issue Attributes テーブルの attr_name 列に文字列 date_reported が入っている行が issue_id ごとに存在することを保証する制約

    • SQL はそのような制約はサポートしていない

    • 行の存在を保証するためのアプリケーションコードが必要




SQL のデータ型を使えない

EAV 設計だと、date_reported に入る値の型・フォーマットを制限できない


EAV/anti/insert-eav.sql

INSERT INTO IssueAttributes (issue_id, attr_name, attr_value)

VALUES (1234, 'date_reported', 'banana');

従来型のデータベースでは、date_reported 列を DATE 型で定義すればよい

EAV 設計を拡張して、各 SQL データ型のために個別の attr_value 列を定義することもある


EAV/anti/data-types.sql

SELECT issue_id, COALESCE(attr_value_date, sttr_value_datetime,

attr_value_integer, attr_value_numeric, attr_value_float,
attr_value_string, sttr_value_text) AS date_reported
FROM IssueAttributes
WHERE attr_name = 'date_reported';

※ COALESCE: 引数の最初の非 NULL な値を返す。参考

様々なデータ型を扱えるようになるが、


  • クエリが複雑化する

  • ユーザ定義データ型 (UDT) やドメイン (DOMAIN) などをサポートしたけらば、さらに列を追加する必要がある

といった問題がある


参照整合性を強制できない

従来型のデータベースでは、外部キー制約によって属性の値を制限できる

例: status 属性の値を BugStatus テーブルが格納している値の1つであることに強制


EAV/anti/foreign-key-plain.sql

CREATE TABLE Issues (

issue_id SERIAL PRIMARY KEY,
-- 他の列 ...
status VARCHAR(20) NOT NULL DEFAULT 'NEW',
FOREIGN KEY (status) REFERENCES BugSTatus(status)
);

EAV 設計では、attr_value 列にこの制約を設定することができない。参照整合性制約は、テーブルの全ての行に適用されてしまうため。


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

あるバグは date_reported という文字列を、別のバグは report_date という文字列を attr_name に格納していることに気づいた場合、正確にバグを数えるには?


EAV/anti/count.sql

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;


  • バグがさらに別の名前で属性を格納してしまっている場合、どうやってこれを確認するか?

  • 1つのバグが別々の名前で、2回同じ属性を格納するミスはどうやって防ぐか?

といった問題が残る

attr_name 列にあらかじめ定義した属性名を格納する参照テーブルへの外部キーを定義することが考えられるが、各エンティティに随時その場で新たに定義される属性をサポートできない (これは従来型の設計でも同様では?)


5.2.3 行を再構築しなければならない

従来型の設計は、Issue テーブルの行に全ての属性が列として格納されている

issue_id
date_reported
status
priority
description

1234
2009-06-01
NEW
HIGH
保存処理に失敗する

EAV 設計でも、従来型のテーブルに格納されているかのように1つの行として取得したい


EAV/anti/reconstruct.sql

SELECT i.issue_id,

i1.attr_value AS date_reported,
i2.attr_value AS status,
i3.attr_value AS priority,
i4.attr_value AS description
FROM Issues AS i
LEFT OUTER JOIN IssueAttributes AS i1
ON i.issue_id = i1.issue_id AND i1.attr_name = 'date_reported'
LEFT OUTER JOIN IssueAttributes AS i2
ON i.issue_id = i2.issue_id AND i2.attr_name = 'status'
LEFT OUTER JOIN IssueAttributes AS i3
ON i.issue_id = i3.issue_id AND i3.attr_name = 'priority'
LEFT OUTER JOIN IssueAttributes AS i4
ON i.issue_id = i4.issue_id AND i4.attr_name = 'description'
WHERE i.issue_id = 1234;

属性の数が増加すると結合の数も増加し、このクエリの実行コストも指数関数的に増加してしまう


5.3 アンチパターンの見つけ方

以下のような言葉を耳にしたら、EAV アンチパターンが採用されている可能性がある


  • 「このデータベースは、メタデータの変更なしで拡張可能。実行時に新しい属性を定義できる」

  • 「クエリは、結合を最大いくつまでサポートしているんだっけ?」

  • 「Eコマースプラットフォームのレポート生成を行うコードの書き方がわからない。コンサルタントを雇わなくては」


5.4 アンチパターンを用いても良い場合

リレーショナルデータベースで EAV アンチパターンの使用を正当化する理由は簡単には見つけられない。EAV の採用による作業の増加やリスクに責任が持てるのなら、消去法的に EAV を選択してもよい。

非リレーショナルなデータ管理が必要なら、非リレーショナルな技術を使うべき

など


5.5 解決策: サブタイプのモデリングを使う


  • サブタイプの数が限られている

  • 開発者が各サブタイプの属性をよく知っている

上記の条件をみたす場合は、EAV を使わずに、EAV が扱うようなデータを格納する解決策がある


5.5.1 シングルテーブル継承


  • 全てのタイプの属性を個別の列に格納し、すべてのサブタイプを1つのテーブルに格納

  • 1つの属性列を、その行がどのサブタイプであるかを定義するのに使用


EAV/soln/create-sti-table.sql

CREATE TABLE Issues (

issue_id SERIAL PRIMARY KEY,
reported_by BIGINT UNSIGNED NOT NULL,
product_id BIGINT UNSIGNED,
priority VARCHAR(20),
version_resolved VARCHAR(20),
status VARCHAR(20),
issue_type VARCHAR(10), -- 'BUG' または 'FEATURE' が格納される
severity VARCHAR(20), -- Bug のみが使う属性
version_affected VARCHAR(20), -- Bug のみが使う属性
sponsor VARCHAR(50), -- FeatureRequest のみが使う属性
FOREIGN KEY (reported_by) REFERENCES Accounts(account_id)
FOREIGN KEY (product_id) REFERENCES Products(product_id)
);


良い点


  • 最もシンプルな設計


悪い点


  • 非 NULL の値を持つ列がバラバラに点在する

  • 新しいオブジェクトタイプの属性が依存のテーブルに存在しない場合は、テーブル定義の変更が必要

  • どの属性がどのサブタイプに所属するかを定義するメタデータがない


    • 属性とサブタイプの対応関係は自前で管理する必要




どのような時に使う?


  • サブタイプの数と、サブタイプ固有の属性の数が少ない

  • Active Record のような単一のテーブルに対するデータベースアクセスパターンを使う必要がある


5.5.2 具象テーブル継承


  • サブタイプごとにテーブルを作成

  • 全てのテーブルは、基底型に共通する属性と、それぞれのサブタイプに固有の属性を含んでいる


EAV/soln/create-concrete-tables.sql

CREATE TABLE Bugs (

issue_id SERIAL PRIMARY KEY,
reported_by BIGINT UNSIGNED NOT NULL,
product_id BIGINT UNSIGNED,
priority VARCHAR(20),
version_resolved VARCHAR(20),
status VARCHAR(20),
severity VARCHAR(20), -- Bug のみが使う属性
version_affected VARCHAR(20), -- Bug のみが使う属性
FOREIGN KEY (reported_by) REFERENCES Accounts(account_id),
FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

CREATE TABLE FeatureRequests (
issue_id SERIAL PRIMARY KEY,
reported_by BIGINT UNSIGNED NOT NULL,
product_id BIGINT UNSIGNED,
priority VARCHAR(20),
version_resolved VARCHAR(20),
status VARCHAR(20),
sponsor VARCHAR(50), -- FeatureRequest のみが使う属性
FOREIGN KEY (reported_by) REFERENCES Accounts(account_id),
FOREIGN KEY (product_id) REFERENCES Products(product_id)
);



良い点


  • サブタイプに存在しない属性列を格納する必要がない

  • 行ごとのサブタイプを表す列が不要


悪い点


  • 共通の属性と、サブタイプ固有の属性の区別が難しい


    • テーブルを初めて見た時、論理的な関係性が存在するのか、偶然テーブルに類似性があるのか不明



  • 共通属性に新しい属性を加える場合、全てのサブタイプのテーブルを変更する必要

  • サブタイプによらず全てのオブジェクトを取得したい場合の処理が複雑になる


    • 共通属性のみを選択し、UNION したビューを定義するという手段がある




EAV/soln/view-concrete.sql

CREATE VIEW Issues AS

SELECT b.issue_id, b.reported_by, ... 'BUG' AS issue_type
FROM Bugs AS b
UNION ALL
SELECT f.issue_id, f.reported_by, ... 'FEATURE' AS issue_type
FROM FeatureRequests AS f;


どのような時に使う?


  • すべてのサブタイプをまたいだ検索をする頻度が低い場合


5.5.3 クラステーブル継承


  • テーブルをオブジェクト指向のクラスであるかのように見なし、継承を模倣

  • 全てのサブタイプに共通する属性を含む基底型のテーブルを1つ作成

  • サブタイプごとに1つずつテーブルを追加し、基底型テーブルに対する外部キーの役割を持つ主キーを設定


EAV/soln/create-class-tables.sql

CREATE TABLE Issues (

issue_id SERIAL PRIMARY KEY,
reported_by BIGINT UNSIGNED NOT NULL,
product_id BIGINT UNSIGNED,
priority VARCHAR(20),
version_resolved VARCHAR(20),
status VARCHAR(20),
FOREIGN KEY (reported_by) REFERENCES Accounts(account_id),
FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

CREATE TABLE Bugs (
issue_id BIGINT UNSIGNED PRIMARY KEY,
severity VARCHAR(20),
version_affected VARCHAR(20),
FOREIGN KEY (issue_id) REFERENCES Issues(issue_id)
);

CREATE TABLE FeatureRequests (
issue_id BIGINT UNSIGNED PRIMARY KEY,
sponsor VARCHAR(50),
FOREIGN KEY (issue_id) REFERENCES Issues(issue_id)
);



良い点


  • メタデータによって、1対1の関連が強制される


    • 主キーを含む外部キー制約



  • 基底型の属性のみを参照する場合に、全てのサブタイプにまたがる検索を効率よく行える


    • 基底テーブルからの検索結果 => サブタイプテーブルへのクエリ



  • 基底テーブルの行から、行がどのサブタイプを表すかを特定する必要はない


    • サブタイプの数が少なければ、すべてをJOINするクエリが書けるため




EAV/soln/select-class.sql

SELECT i.*, b.*, f.*

FROM Issues AS i
LEFT OUTER JOIN Bugs AS b USING (issue_id)
LEFT OUTER JOIN FeatureRequests AS f USING (issue_id);


悪い点


  • 結局、サブタイプの数が多くなると対応できない

  • 新しい属性の追加には、テーブルの更新が必要


どのような時に使う?


  • 全てのサブタイプに共通する列を参照するクエリが頻繁に実行される場合


5.5.4 半構造化データ


  • LOB 列を追加し、XML や JSON で属性名と値を共に格納


    • LOB: Large Object (BLOB, CLOB, TEXT 型など)




EAV/soln/create-blob-tables.sql

CREATE TABLE Issues (

issue_id SERIAL PRIMARY KEY,
reported_by BIGINT UNSIGNED NOT NULL,
product_id BIGINT UNSIGNED,
priority VARCHAR(20),
version_resolved VARCHAR(20),
status VARCHAR(20),
issue_type VARCHAR(10), -- 'BUG' または 'FEATURE' が格納される
attributes TEXT NOT NULL, -- その他の動的属性が格納される
FOREIGN KEY (reported_by) REFERENCES Accounts(account_id),
FOREIGN KEY (product_id) REFERENCES Products(product_id)
);


良い点


  • 拡張性が極めて高い


悪い点


  • SQL が特定の属性にアクセスする手段をほとんど持っていない


    • LOB 列を1つの値として取得し、アプリケーション側で処理する必要がある




どのような時に使う?


  • サブタイプの数を制限できない場合

  • 新しい属性を随時定義するための高い柔軟性が必要な場合


5.5.5 後処理

どうしても EAV を使わざるを得ない状況がある


  • プロジェクトを引き継いだ場合

  • EAV を使用するサードパーティ製のソフトウェアプラットフォームを採用している場合

EAV を使うためには、追加の作業が必要になることを想定しておく

データが従来型のテーブルに格納されているかのように、単一行としてエンティティを取得するクエリを書いてしまわないように注意

以下のように、特定のエンティティと関連付けられた属性をクエリの対象とし、行の集合として取得する


EAV/soln/post-process.sql

SELECT issue_id, attr_name, attr_value

FROM IssueAttributes
WHERE issue_id = 1234;


result

mysql> SELECT issue_id, attr_name, attr_value FROM IssueAttributes WHERE issue_id = 1234;

+----------+---------------+-----------------------------+
| issue_id | attr_name | attr_value |
+----------+---------------+-----------------------------+
| 1234 | date_reported | 2009-06-01 |
| 1234 | description | 保存処理に失敗する |
| 1234 | priority | HIGH |
| 1234 | Product | Open RoundFile |
| 1234 | reported_by | Bill |
| 1234 | severity | 機能の損失 |
| 1234 | status | NEW |
+----------+---------------+-----------------------------+

このクエリは、以下の点で優れている


  • ユーザにとって作りやすい

  • データベースにとって処理しやすい

  • クエリの作成時に属性の数を知る必要がない

この形式で結果を扱うには、アプリケーションでオブジェクトのプロパティとして設定する必要がある


EAV/soln/post-process.rb

require 'mysql2'

objects = {}

stmt = mysql_client.query(
"SELECT issue_id, attr_name, attr_value
FROM IssueAttributes
WHERE issue_id = 1234"

)

stmt.each do |row|
id = row["issue_id"]
field = row["attr_name"]
value = row["attr_value"]

objects[id] ||= {}
objects[id][field] = value
}


このように、後処理が面倒な仕事になってしまう

EAV を使用するということは、異なる属性が異なる列に格納する方法が確立されている SQL の上に、属性を識別するための新たなレイヤーすなわち「システム内のシステム」を構築するということを意味する