SQL Antipatterns ch6 Entity-Attribute-Value
- Entity-Attribute-Value
- Objective: Support Vairable Attribute
- Antipattern: Use a Generic Attribute Table
- How to Recognize the Antipattern
- Legitimate Use of the Antipattern
- Solution: Model the Subtypes
Use metadata for metadata.
Entity-Attribute-Value
Issues
issue_id(PK) |
---|
1234 |
IssueAttributes
issue_id(PK) | attr_name(PK) | attr_value |
---|---|---|
1234 | date_reported | 2009-06-01 |
1234 | description | Saving does not work |
1234 | priority | HIGH |
1234 | product | Open RoundFile |
1234 | reported_by | Bill |
1234 | severity | loss of functionality |
1234 | status | NEW |
こういうやつ
Objective: Support Vairable Attribute
Antipattern: Use a Generic Attribute Table
- 典型的には次の3カラムから成るテーブルを作ってしまう
- Entity
- 1つのオブジェクトを特定する属性
- 親テーブルを参照するFK
- 冒頭の例では
issue_id
- Attribute
- オブジェクトの属性名
- 冒頭の例では
attr_name
- Value
- オブジェクトの属性値
- 冒頭の例では
attr_value
- Entity
- Entity-Attribute-Valueというパターン名は上記カラム分類より
- open schema, schemaless, name-value pairs とも
CREATE TABLE Issues ( issue_id SERIAL PRIMARY KEY ); CREATE TABLE IssueAtributes ( 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) );
- メリット
- カラムが少ない
- 新しい属性を追加しても増えない
- N/AのNULLを回避できる(単にレコードを登録しないだけ)
- カラムが少ない
- デメリット甚大
Querying an Attribute
- issueの報告日を取得するクエリ
- 普通の設計ならこう
SELECT issue_id, date_reported FROM Issues;
- EAVだとこう
SELECT issue_id, attr_value AS date_reported FROM IssueAttributes WHERE attr_name = 'date_reported';
- 冗長
- SELECTとWHEREに同じ属性名
- わかりにくい
Supporting Data Integrity
- RDB上で必須属性を作れない
- 普通のDB設計ならカラムにNOT NULLをつけるだけの仕事
- EAVでは困難
- レコードで属性名-属性値ペアを表現してしまっているため
- 「特定のカラムが特定の値をもつ」というレコードの制約はない
- SQLのデータ型を利用できない
- 汎用VARCHARにせざるをえない
- 不正値を除外できない
attr_value_integer
,attr_value_datetime
などを追加する人もいるが事態は悪化する
- 汎用VARCHARにせざるをえない
- 参照整合性を課すことができない
- FK制約はすべてのレコードに適用される
- 条件付きでFK制約を課すことはできない
attr_name
が'status'の場合のみBugStatus.status
へのFK制約を課す、といったこと
- 属性名の揺れへの耐性がない
attr_name
が'date_reported', 'report_date'で揺れても登録できてしまう- クエリは悲惨なことに
Reconstructing a Row
- 通常の列持ちテーブルを得るには属性名の数だけ
LEFT OUTER JOIN
が必要 - 属性の種類が増えるとコスト甚大に
How to Recognize the Antipattern
- こんなのが聞こえてきたら注意
Legitimate Use of the Antipattern
Solution: Model the Subtypes
- PoEAAのパターンを使う
- Single Table Inheritance
- Concrete Table Inheritance
- Class Table Inheritance
- Serialized LOB
- そのうちPoEAA読むので略