SQLアンチパターン：EAV（エンティティ・アトリビュート・バリュー）

SQLアンチパターンを読んだ。とても良かったです。

SQLアンチパターン

「SQLあるある言いたい〜」な気分になったので、記載されていた中の一つ「EAV（エンティティ・アトリビュート・バリュー）」についてまとめる。

EAV（エンティティ・アトリビュート・バリュー）とは

オブジェクトで以下のような構造があるとする。

[Issueオブジェクト]
・issue_id
・data_reported
・reporter
・priority
・status

[Bugオブジェクト（Issueを継承。Issueのサブタイプ）]
・severity
・version_affected

[FeatureRequestオブジェクト（Issueを継承。Issueのサブタイプ）]
・sponsor

これをテーブルでどのように保持させるかだが、汎用的な属性テーブルを定義して使う以下のようなものがEAV（エンティティ・アトリビュート・バリュー）だそうだ。

[Issuesテーブル]
・issue_id int

[IssueAttributesテーブル（汎用的な属性テーブル）]
・issue_id INT
・attr_name VARCHAR(100)
・attr_value VARCHAR(100)

これに上記のオブジェクトのデータを保存する時、以下のように行う。

INSERT INTO Issues (issue_id) VALUES (1);
INSERT INTO IssueAttributes (issue_id, attr_name, attr_value)
 VALUES
  (1, ‘date_reported’, ‘2009-06-01’), 
  (1, ’reporter’, ’Bill’),
  (1, ’priority’, ’HIGH’),
  (1, ’status’, ’NEW’), 
  (1, ’severity’, ’機能の損失’),
  (1, ’version_affected’, ’1.0’)

問題点

本にはいろいろ親切に書いてくれているが、見ただけでいろんな害があるのがわかる。
あらゆるSQL文がややこしくなりそう。
幸いこういうテーブルに出会ったことはない。

解決策は？

シングルテーブル継承

一つのテーブルにすべてのサブタイプで必要な属性を含める。
issue_typeによって、どのサブタイプのデータなのか判断できる。

[Issuesテーブル]
・issue_id
・data_reported
・reporter
・priority
・status
・issue_type（BUG or FEATURE）が格納される。
・severity（Bugサブタイプにのみ必要な属性）
・version_affected（Bugサブタイプにのみ必要な属性）
・sponsor（FeatureRequestサブタイプにのみ必要な属性）

問題点

サブタイプ毎に使われている属性を判断する情報がない。
例えば「issue_typeがBUGの時はseverityとversion_affectedの値を参照するべき」という情報がない。

具象テーブル継承

サブタイプ毎にそれぞれテーブルを定義する。

[Bugsテーブル]
・issue_id
・data_reported
・reporter
・priority
・status
・severity（Bugサブタイプにのみ必要な属性）
・version_affected（Bugサブタイプにのみ必要な属性）

[FeatureRequestsテーブル]
・issue_id
・data_reported
・reporter
・priority
・status
・sponsor（FeatureRequestサブタイプにのみ必要な属性）

問題点

共通部分に変更を加える場合、すべての関連するサブタイプのテーブルを変更しなくてはならない。

共通部分であることを判断する情報がない。
例えばreporterカラムは共通部分であるが、それが共通部分であるという判断をどうやってするか？全てのサブタイプテーブルの構造すべてに含まれていることを確認する？すべてのサブタイプテーブル一覧をどうやって知ればいいか？

共通部分データをつかって横断的にデータを取得したい場合にすこし面倒

クラステーブル継承

オブジェクト指向のクラスと同じような形でテーブル定義をする

[Issuesテーブル]
・issue_id
・data_reported
・reporter
・priority
・status

[Bugsテーブル]
・issue_id
・severity（Bugサブタイプにのみ必要な属性）
・version_affected（Bugサブタイプにのみ必要な属性）

[FeatureRequestsテーブル]
・issue_id
・sponsor（FeatureRequestサブタイプにのみ必要な属性）

問題点

特になし

半構造化データ

サブタイプのデータをXMLやJSONなど構造化テキストデータにして保持させる
サブタイプの定義を動的に行いたい場合などに適しているとのこと。

[Issuesテーブル]
・issue_id
・data_reported
・reporter
・priority
・status
・issue_type（BUG or FEATURE）が格納される。
・attributes　その他の属性がXMLやJSONなどの形で保存される

問題点

アンチパターンのジェイウォークな感じがある。
SQLの恩恵に預かりにくい構造である。

まとめ

特殊な事情がない限り、クラステーブル継承がベストプラクティスかと思った。

現場では「テーブルの数が増える」が嫌われる場合があり「シングルテーブル継承」を求められる場合が多い気がする。
シングルテーブル継承でも大体問題ないが、記載している通り「どのサブタイプがどのデータを保持しているか」の情報が欠落しているので（「みればわかるでしょ」的に押し切られる）データ構造的には「クラステーブル継承」の形が優れていると思う。