SQLアンチパターン 5章 EAV
https://scrapbox.io/files/65fe36fbd8e1880025829690.png
5章 EAV（エンティティ・アトリビュート・バリュー）
自分なりのまとめ
EAV 設計は、リレーショナルデータベースのメリットを損なうのでやめよう
EAV 設計とは、別のテーブルを作成して属性を「行」に格納する方法
EAV を採用するモチベーションは、可変属性のサポート
解決策として、サブタイプのモデリングをしよう
シングルテーブル継承 (STI）
サブタイプの数とサブタイプ固有の属性の数が少なく、アクティブレコードのような単一のテーブルに対するデータベースアクセスパターンを使う必要がある場合 具象テーブル継承
すべてのサブタイプを跨いだ検索を実行する頻度が低い場合
クラステーブル継承
すべてのサブタイプに共通する列を参照するクエリが頻繁に実行されるとき
半構造化データ
サブタイプの数を制限できない場合、新しい属性を随時定義するための高い柔軟性が必要な場合
どの解決策を採用するかは、データをどのように検索したいかに拠る
↓ テーブルの attr_name と attr_value に Key/Value で様々な値が入っているような構造が EAV
code:mermaid
erDiagram
Issues ||--o{ IssueAttributes : "Issueは複数のIssueAttributesを保持する"
Issues {
bigint issue_id PK
}
IssueAttributes {
references issue_id PK
VARCHAR(100) attr_name PK
VARCHAR(100) attr_value
references Issue(issue_id) FK
}
可変属性をサポートしたい場面
リレーショナルデータベースのメタデータの柔軟性が低いという問題はずっと議論されていて、可変属性をサポートしたいことがある
バグデータベースの例で考える
Issue: 基底型
Bug: バグ 独自の属性（バグが発生した製品のバージョン、バグの重大度）
FeatureRequest: 機能要望 独自の属性（機能要望の開発に出資しているスポンサー）
code:mermaid
classDiagram
Issue <|-- Bug
Issue <|-- FeatureRequest
Issue: Date_reported
Issue: Reporter
Issue: Priority
Issue: Status
Bug: Severity
Bug: Version_affected
FeatureRequest: Sponsor
アンチパターン: 汎用的な属性テーブルを使用する（EAV設計）
可変属性をサポートする必要があるとき、もう1つ別のテーブルを作成して属性を「行」に格納する解決策が魅力的に思える
このような設計を、エンティティ・アトリビュート・バリュー（Entity-Attribute-Value)、略して EAV と呼ぶ
オープンスキーマ、スキーマレス、名前/値ペア と呼ばれることも
処理の複雑さは解消できない！
code:mermaid
erDiagram
Issues ||--o{ IssueAttributes : "Issueは複数のIssueAttributesを保持する"
Issues {
bigint issue_id PK
}
IssueAttributes {
references issue_id PK
VARCHAR(100) attr_name PK
VARCHAR(100) attr_value
references Issue(issue_id) FK
}
code: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 VARCHAR(100) NOT NULL,
attr_value VARCHAR(100)
PRIMARY KEY (issue_id, attr_name),
FOREIGN KEY (issue_id) REFERENCES Issues(issue_id)
);
INSERT INTO IssueAttrubutes (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_affcted', '1.0'),
(1234, 'severity', '機能の損失'),
(1234, 'priority', 'HIGH');
属性を取得するにはどうするか
報告日別に全てのバグを検索するクエリ
属性を取得するクエリが冗長になり、明確さも低下する 😢
code:sql
--- EAV以外の場合、簡潔なクエリ！
SELECT issue_id, date_reported FROM Issue;
--- EAVの場合、ややこしいな
SELECT issue_id, attr_value AS date_reported
FROM IssueAttributes
WEHRE attr_name = 'date_reported;
データ整合性をどう保つか
EAV を使うと、従来型の DB 設計で得られる利点をいくつも失ってしまう
必須属性を設定できない
NULL NULL制約を使えない
属性は列ではなく、IssueAttributes テーブルの行に格納されている
attr_name 列に文字列 'date_reported' が入っている行が issue_id ごとに存在することを保証する制約は SQL でサポートしていないので、アプリケーションコードで担保しなければいけなくなる（つら）
SQL のデータ型を使えない
通常の EAV 設計では、attr_valud 列のデータ型を、あらゆる種類の属性を格納できるように文字列型にする
日付形式などの DATE 型を使えない
参照整合性を強制できない
従来、参照テーブルに対する外部キーを定義することで、一部の属性値を制限できる
EAV 設計では、参照整合性はテーブルのすべての行に適用されてしまうため、参照整合性を設定できない
属性名を補わなければならない
属性名の文字列に一貫性がない場合がある date_reported, report_date
EAVでは、動的に属性が増えることが一般的
行を再構築しなければならない
EAV 設計では、各属性が IssueAttributes テーブルの個別の行に格納されるため、全ての属性を行の一部分として取得するには、各属性の JOIN が必要になる
属性の1つがテーブルに行として存在していない場合、内部結合（INNER JOIN）を使うと結果がゼロ行になってしまうため、外部結合（OUTER JOIN）も使わねばならない
属性の数が増えると結合の数も増加し、クエリの実行コストも指数関数的に増加する 😇
アンチパターンの見つけ方
「このデータベースは、メタデータの変更なしで拡張可能だ、実行時に新しい属性を定義できる」
「クエリは、結合を最大いくつまでサポートしているんだっけ？」
導入時の初期設定が不要で、裏でデータベースを使っているソフトウェアパッケージの多くは、カスタマイズしやすいように、EAV 設計を採用している
アンチパターンを用いてもよい場合
リレーショナルデータベースで、EAV の使用を正当化する理由は簡単には見つからない
スキーマレスなデータを必要とするアプリケーションで求められているのは、ごく少数のテーブルのみ
非リレーショナルなデータ管理が必要なら、非リレーショナルな技術を使うべき
Berkeley DB、Cassandra、CouchDB、Haddop/HBase、MongoDB、Redis、Tokyo Cabinet etc.
EAVの弱点は、これらの代替手法にも当てはまる
メタデータが流動的であると、シンプルなクエリの作成が難しくなる 😭
解決策: サブタイプのモデリングを行う
解決策がうまく機能するのは、サブタイプの数が限られ、開発者が各サブタイプの属性をよく知っているとき
どの解決策を採用するかは、データをどのように検索したいかに拠る
1. シングルテーブル継承
最もシンプルな設計
すべてのタイプの属性を個別の列に格納し、関連するすべてのサブタイプを1つのテーブルに格納すること
1つの属性列を、その行がどのサブタイプであるかを定義するために使う
属性によっては、すべてのサブタイプに共通のものがある
多くの属性は、サブタイプ固有のもの
対応する属性を持たないオブジェクトを格納するには、NULLを入れなくてはならない
欠点: どの属性がどのサブタイプの所属するかを定義するメタデータがない
属性とサブタイプの対応関係は、開発者が自前で管理せねばならぬ
採用が適切な場面
サブタイプの数とサブタイプ固有の属性の数が少なく、アクティブレコードのような単一のテーブルに対するデータベースアクセスパターンを使う必要がある場合 code:mermaid
erDiagram
Issues {
bigint issue_id PK
bigint reported_by
bigint product_id
VARCHAR(20) priority
VARCHAR(20) vesion_resolved
VARCHAR(20) status
VARCHAR(10) issue_type "サブタイプの識別用カラム"
VARCHAR(20) severity "Bugのみが使う"
VARCHAR(20) version_affected "Bugのみが使う"
VARCHAR(50) sponsor "FeatureRequestsのみが使う"
}
code:sql
CREATE TABLE Issues (
issue_id SERIAL PRIMARY KEY,
reported_by BIGINT UNSIGNED NOT NULL,
product_id BIGINT UNSIGNED,
priority VARCHAR(20),
vesion_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)
);
2. 具象テーブル継承
サブタイプごとにテーブルを作成する
すべてのテーブルは、基底型に共通する属性と、サブタイプ固有の属性を含む
具象テーブル継承（Concrete Table Inheritance）
STI と比較したメリットは、サブタイプに存在しない属性列を格納する必要がない点
行ごとのサブタイプを表す列が不要になるメリット
すべてのサブタイプに共通する属性と、サブタイプ固有の属性の区別は簡単ではない
共通属性に新しい属性を加える場合、全てのサブタイプのテーブルを変更しなければならない 😨
サブタイプテーブルに格納されたデータが、基底型とサブタイプのどちらに属するか示すメタデータはない
すべてのオブジェクトを、どのサブタイプであるかにかかわらず取得したい場合の処理が複雑になる
別テーブルに格納されているため
このクエリを簡単にするために、共通属性のみを選択し、テーブルをUNIONしたビューを定義するという手段あり
すべてのサブタイプを跨いだ検索を実行する頻度が低い場合に適切
code:mermaid
erDiagram
Bugs {
bigint issue_id PK
bigint reported_by
bigint product_id
VARCHAR(20) priority
VARCHAR(20) vesion_resolved
VARCHAR(20) status
VARCHAR(20) severity "Bugのみが使う"
VARCHAR(20) version_affected "Bugのみが使う"
}
FeatureRequests {
bigint issue_id PK
bigint reported_by
bigint product_id
VARCHAR(20) priority
VARCHAR(20) vesion_resolved
VARCHAR(20) status
VARCHAR(50) sponsor "FeatureRequestsのみが使う"
}
code:sql
CREATE TABLE Bugs (
issue_id SERIAL PRIMARY KEY,
reported_by BIGINT UNSIGNED NOT NULL,
product_id BIGINT UNSIGNED,
priority VARCHAR(20),
vesion_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),
vesion_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)
);
3. クラステーブル継承
テーブルをオブジェクト指向のクラスであるかのようにみなして、継承を模倣する
すべてのサブタイプに共通する属性を含む基底型のテーブルを1つ作る
サブタイプごとに1つずつ追加のテーブルを作成、基底型テーブルに対する外部キーの役割を持つ主キーを設定
クラステーブル継承（Class Table Inferitance）
メタデータによって、1対1の関係が矯正される
子側のテーブルの外部キーは主キーでもあるため、一意になるから
code:mermaid
erDiagram
Issues ||--o{ Bugs : "BugsはIssuesを参照する"
Issues ||--o{ FeatureRequests : "FeatureRequestsはIssuesを参照する"
Issues {
serial issue_id PK
bigint reported_by
bigint product_id
VARCHAR(20) priority
VARCHAR(20) vesion_resolved
VARCHAR(20) status
}
Bugs {
bigint issue_id PK
VARCHAR(20) severity
VARCHAR(20) version_affected
references Issue(issue_id) FK
}
FeatureRequests {
bigint issue_id PK
VARCHAR(50) sponsor
references Issue(issue_id) FK
}
code:sql
CREATE TABLE Issues (
issue_id SERIAL PRIMARY KEY,
reported_by BIGINT UNSIGNED NOT NULL,
product_id BIGINT UNSIGNED,
priority VARCHAR(20),
vesion_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), -- Bug のみが使う
version_affected VARCHAR(20), -- Bug のみが使う
FOREIGN KEY (issue_id) REFERENCES Issues(issue_id)
);
CREATE TABLE FeatureRequests (
issue_id BIGINT UNSIGNED PRIMARY KEY,
sponsor VARCHAR(50), -- FeatureRequest のみが使う
FOREIGN KEY (issue_id) REFERENCES Issues(issue_id)
);
基底型の属性のみを参照する限り、すべてのサブタイプにまたがる検索を効率よく行える
検索条件と一致するエントリを基底テーブルから取得後、各サブタイプテーブルに対してクエリすることで、サブタイプ固有の属性を取得できる
基底テーブルの行から、行がどのサブタイプを表すかを特定する必要はない
サブタイプの数が少ない限り、これらを全てJOINするクエリを書けるから（ビュー定義の候補にもなる）
STI のように、NULLが点在する結果集合が得られる（サブタイプに存在しない属性はNULLになる）
code: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)
すべてのサブタイプに共通する列を参照するクエリが頻繁に実行されるときに適している
4. 半構造化データ
サブタイプの数が多い場合、頻繁に新しい属性を追加しなければならない場合、LOB列を追加し、XMLやJSONの形式で属性名と値を共に格納することもできる
シリアライズLOB（Serialized LOB）とマーティンファウラーは呼んでる
長所: 拡張性が極めて高い点
短所: SQL が特定の属性にアクセスする手段をほとんど持っていない
LOB 列は、行の絞り込み、集約計算、ソートなどの処理のために LOB 内の個別の属性を選択することが難しい
まず LOB 全体を1つの値として取得し、属性に分解して解釈するアプリケーションコードを書かねばならない
サブタイプの数を制限できない場合、新しい属性を随時定義するための高い柔軟性が必要な場合に適している
code:mermaid
erDiagram
Issues {
bigint issue_id PK
bigint reported_by
bigint product_id
VARCHAR(20) priority
VARCHAR(20) vesion_resolved
VARCHAR(20) status
VARCHAR(10) issue_type "サブタイプの識別用カラム"
TEXT attributes "その他の動的属性が格納される"
}
code:sql
CREATE TABLE Issues (
issue_id SERIAL PRIMARY KEY,
reported_by BIGINT UNSIGNED NOT NULL,
product_id BIGINT UNSIGNED,
priority VARCHAR(20),
vesion_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)
);
5. 後処理
どうしても EAV 設計を使わざるを得ない状況がある
特定のエンティティと関連付けられた属性をクエリの対象とし、それらが格納されているのと同じかたち、つまり行の集合として取得するようにする
このクエリは、ユーザーにとって作りやすく、データベースにとって処理しやすい
クエリ作成時に属性の数を知らなくても、Issueに関連づけられた属性が全て返される
しかしこれをアプリケーションコードで結果行をループするコードを書き、オブジェクトのプロパティとして設定するには、複雑なコードになってしまう
code:sql
SELECT issue_id, attr_name, attr_value
FROM IssueAttributes
WHERE issue_id = 1234;
table:EAV属性を行の集合として取得する
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
SQL では、異なる属性は異なる列に格納するという方法が確立されている
EAV を使うということは、属性を識別するための新たな方法のレイヤーを SQL 上に構築することを意味する
SQL が EAV の処理をぎこちなく非効率にしか扱えないのも無理はない
メタデータはメタデータのために用いましょう