こちらの記事では、「SQLアンチパターン」に登場する EAV（エンティティ・アトリビュート・バリュー）についてまとめたものになります。
EAV（エンティティ・アトリビュート・バリュー）とは
EAV は、ソフトウェア開発者が将来の変更に対してごくわずかな変更で対応したり、全く手を入れずに対応できるような柔軟なソフトウェアを設計したいという考えから生まれるアンチパターンです。
EAV は、もう一つ別のテーブルを作成して、属性を「行」に格納します。例えば、以下のようなテーブル設計です。
CREATE TABLE Issues (
issue_id SERIAL PRIMARY KEY
);
CREATE TABLE IssueAttributes (
issue_id BIGINT UNSIGNED NOT NULL,
attr_name VARCHAR(100) NOT NULL,
att_value VARCHAR(100),
PRIMARY KEY (issue_id, attr_name),
FOREIGN KEY (issue_id) REFERENCES Issues(issue_id)
);
INSERT INTO Issue(issue_id) VALUES('1234');
INSERT INTO IssueAttribute (issue_id, attr_name, attr_value) VALUES
(1234, 'product', '1'),
(1234, 'date_reported', '2009-06-01'),
(1234, 'status', 'NEW'),
(1234, 'description', '保存処理に失敗する'),
(1234, 'reported_by', 'Bill'),
(1234, 'version_affected', '1.0'),
(1234, 'severity', '機能の損失'),
(1234, 'priority', 'HIGH'); このような設計を EAV（エンティティ・アトリビュート・バリュー）と呼ばれていて、オープンスキーマ、スキーマレス、名前/値ペア と呼ばれることもあります。
EAV のメリットは、列数の削減、新規属性をサポートするために、列数増加をする必要はない、属性が存在しないエンティティの該当列にNULLが入っている、NULLだらけのテーブルを防げます。
EAV（エンティティ・アトリビュート・バリュー）のデメリット
属性を取得する SQL が冗長化する
報告日（date_reported）を取得する際に、Issue テーブルに date_reported が存在するときは、以下の SQL 文を一つ実行すれば、取得可能です。
SELECT issue_id, date_reported FROM Issue;EAV 設計の場合、格納された属性を検索して date_reported をフェッチする必要があります。このクエリは、上記のクエリよりも冗長になります。
SELECT issue_id, attr_value AS date_reported
FROM IssueAttribute
WHERE attr_name = 'date_reported';データの整合性が保てない
EAV 設計では従来型のデータベース設計で得られるいくつもの利点を失ってします。
- 必須属性を設定できない
- 従来のデータベースでは、NOT NULL 制約を宣言することでその列を必須にすることができますが、EAV 設計ですとそれができなくなってしまいます。
- SQL のデータ型を使用できない
- 色々な種類のデータが挿入されるので、データ型を設定することができません。そうなると、DATE，INT型などのフォーマットを指定することができません。
- 参照整合性を強制できない
- 外部キー制約を設定してしまうと、挿入されている全ての属性に対して外部キーが設定されることになってしまいます。
- 属性の補完が必要
- 属性名が一貫性のないようなものになる可能性があります。例えば、date_reported, report_dateなどで、同じものを指しているはずなのに、属性名が多様にあるとそれだけで管理できなくなってしまいます。
行を再構築する必要がある
EAV 設計では、各属性が IssueAttributes テーブルの各行に挿入されているので、全ての属性を行の一部として取得するためには、各属性の行の結合ｗが必要になります。
クエリの作成時には、属性の名前をすべて指定しなければなりません。
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 Issue AS i
LEFT OUTER JOIN IssueAttribute AS i1
ON i.issue_id = i1.issue_id AND i1.attr_name = 'date_reported'
LEFT OUTER JOIN IssueAttribute AS i2
ON i.issue_id = i2.issue_id AND i2.attr_name = 'status'
LEFT OUTER JOIN IssueAttribute AS i3
ON i.issue_id = i3.issue_id AND i3.attr_name = 'priority'
LEFT OUTER JOIN IssueAttribute AS i4
ON i.issue_id = i4.issue_id AND i4.attr_name = 'description'
WHERE i.issue_id = 1234;EAV（エンティティ・アトリビュート・バリュー）の解決方法
シングルテーブル継承
シングルテーブル継承は、すべてのタイプの属性を個別の列に格納して、関連するすべてのサブタイプを一つのテーブルに格納することです。
シンブルテーブル継承の採用が適切なのは、サブタイプの数とサブタイプ固有の属性の数が少なく、アクティブレコードのような単一のテーブルに対するデータベースアクセスパターンを使う必要がある場合です。
具象テーブル継承
具象テーブル継承とは、サブタイプごとにテーブルを作成することです。
シングルテーブル継承より優れている点としては、サブタイプをに存在しない属性列を格納する必要がないということです。
欠点は、バグか機能要望かに関わらずデータを取得したい場合、テーブルをまたいだ情報を取得する際にのクエリが煩雑化することです。
CREATE VIEW Issue AS
SELECT b.issue_id, b.reported_by, ... , 'BUG' AS issue_type
FROM Bug AS b
UNION ALL
SELECT f.issue_id, f.reported_by, ... , 'FEATURE' AS issue_type
FROM FeatureRequest AS f;クラステーブル継承
クラステーブル継承は、テーブルをオブジェクト指向のクラスであるかのようにみなして、継承を模倣する方法です。
まず、すべてのサブタイプに共通する属性を含む基底型のテーブルを一つ作ります。
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)
);この方法を用いると、基底型の属性のみを参照する限り、すべてのサブタイプにまたがる検索を効率よく行うことができます。
半構造化データ
共通項目などの明確な項目は個別列を作成し、動的に管理する項目をLOB列と呼ばれる列に対して、JSON・XML等の形式で属性名・値を格納する方法です。
CREATE TABLE Issue (
issue_id IDENTITY,
reported_by BIGINT NOT NULL,
product_id BIGINT,
priority VARCHAR(20),
version_resolved VARCHAR(20),
status VARCHAR(20),
issue_type VARCHAR(10), --'BUG'または'FEATURE'を格納
attributes XML NOT NULL, --LOB列：その他の動的属性を格納
FOREIGN KEY (reported_by) REFERENCES Accounts(account_id),
FOREIGN KEY (product_id) REFERENCES Products(product_id)
);こうすることで、項目を追加することが可能なので、拡張性が極めて高いです。
しかし、SQL が特定の属性にアクセスすることができません。SQLServer ではデータ型として XML が用意されており、SQL から直接 XML のデータを操作することも可能です。
まとめ
EAV は基本的に採用しなければならないケースはありません。いろんな状況やデータベースの利用の仕方を踏まえた上で適切な手法を選んで実行していく必要があります。
コメント