勉強日記

チラ裏

SQL Antipatterns ch6 Entity-Attribute-Value

pragprog.com


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

  • OOPのオブジェクトをRDBに格納したい
    • 派生クラスごとに格納する情報が異なる
  • RDBのテーブルに柔軟性・実行時拡張性をもたせたい
    • 1970年代、E.F.Coddによりリレーショナルモデル提唱
    • RDBメタデータに柔軟性がないことは当初から指摘されている

Antipattern: Use a Generic Attribute Table

  • 典型的には次の3カラムから成るテーブルを作ってしまう
    • Entity
      • 1つのオブジェクトを特定する属性
      • 親テーブルを参照するFK
      • 冒頭の例ではissue_id
    • Attribute
      • オブジェクトの属性名
      • 冒頭の例ではattr_name
    • Value
      • オブジェクトの属性値
      • 冒頭の例ではattr_value
  • 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などを追加する人もいるが事態は悪化する
  • 参照整合性を課すことができない
    • 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

  • こんなのが聞こえてきたら注意
    • メタデータを変更することなく完全に拡張可能なDBを作った。実行時に新しい属性を定義できる。」
      • RDBはその種の柔軟性を持ち合わせていない
      • おそらくEAVを踏んでいる
    • 「1クエリで最大何回までJOINできる?」
      • EAVの行持ちテーブルを列持ちに変換するときに大量のJOINが必要なことから
    • 「Eコマースプラットホームでレポートの書き出し方がわからない」
      • 出来合いのDB駆動ソフトウェアパッケージは、カスタマイズ性のためにEAVパターンを採用していたりする
      • レポートを書き出すようなクエリは非常に複雑、あるいは非実用的

Legitimate Use of the Antipattern

  • 正当化するのが困難なアンチパターン
  • RDB以外を使え
    • とはいえEAVの弱点は依然として残る

Solution: Model the Subtypes

  • PoEAAのパターンを使う
    • Single Table Inheritance
    • Concrete Table Inheritance
    • Class Table Inheritance
    • Serialized LOB
  • そのうちPoEAA読むので略

Post-Processing

  • EAVを避けられないことも
    • 引き継いだプロジェクト
    • EAVパターンなサードパーティソフトウェアに依存している
  • 行持ちデータを列持ちデータに変換する必要がある
  • 無理してSQLで変換するな、アプリケーションコードで後処理せよ
    • SQLが提供してくれている「別個のカラムを特定する」仕組みを放棄した以上、SQLでうまく処理できないのは当然
      • ぎこちない
      • 非効率