勉強日記

チラ裏

SQL Antipatterns ch7 Polymorphic Associations (WIP)

SQL 勉強メモ デザインパターン

pragprog.com

Polymorphic Associations

In every table relationship, there is one referencing table and one referenced table.

  • バグトラッキングシステムの例
  • CommentをBugまたはFeature Requestにぶら下がらせたい

Objective: Reference Multiple Parents

  • 1対多、排他的な親子関係
    • Bugs, FeatureRequestsテーブルが親
    • Commentsテーブルが子
    • いずれか一方の親を参照する

Antipattern: Use Dual-Purpose Foreign Key

Defining a Polymorphic Association

  • BugとFeature Requestを「Issue」として汎化し、issue_idで参照する
  • 「いずれか一方」の判別用にissue_typeカラムを持つ
CREATE TABLE COMMENTS (
    comment_id   SERIAL PRIMARY KEY,
    issue_type   VARCHAR(20), -- "Bugs" or "FeatureRequests"
    issue_id     BIGINT UNSIGNED NOT NULL,
    author       BIGINT UNSIGNED NOT NULL,
    comment_date DATETIME,
    comment      TEXT,
    FOREIGN KEY (author) REFERENCES Accounts(account_id)
);
  • issue_idに外部キー制約がない
    • 指定のしようがない
      • 行ごとに異なるテーブルを参照する、ということができないため

Querying a Polymorphic Association

  • 全Bugと全Feature Requestにぶら下がった全コメントを取得するクエリは次のようになる
SELECT c.comment_id
     , c_issue_type
     , c_issue_id
     , c.comment
     , b.issue_id
     , f.issue_id
  FROM Comments c
  LEFT OUTER JOIN Bugs b
    ON (b.issue_id = c.issue_id AND c.issue_type = 'Bugs')
  LEFT OUTER JOIN FeatureRequests f
    ON (f.issue_id = c.issue_id AND c.issue_type = 'FeatureRequests');
c.comment_id c_issue_type c_issue_id c.comment b.issue_id f.issue_id
6789 Bugs 1234 It crashes! 1234 NULL
9876 Feature... 2345 Great Idea! NULL 2345
  • FK制約同様、JOINも「行ごとに異なるテーブルと結合する」ということはできない
  • そのため、Bugs, FeatureRequestsのいずれかは参照先が存在せず、NULLが現れる
  • NULLでも結果集合に含めるためにLEFT OUTER JOIN必須

Non-Object-Oriented Example

  • BugとFeature RequestをIssueに汎化する例はオブジェクト指向が根底にある
  • Polymorphic Associationsは、汎化関係にないエンティティ同士にも適用されうる
    • UsersとOrdersとか
CREATE TABLE Addresses (
    address_id   SERIAL PRIMARY KEY,
    parent       VARCHAR(20),    -- "Users" or "Orders"
    parent_id    BIGINT UNSIGNED NOT NULL,
    users_usage  VARCHAR(20),    -- "billing" or "shipping"
    orders_usage VARCHAR(20),    -- "billing" or "shipping"
    address      TEXT
);
  • ユーザの住所と、注文の配送先住所
  • 住所の使途にも2通りある
    • 請求
    • 配送先
  • 制約を課せないのでコメントでメモすることに
    • 雑草のように伝播する

コラム: Mixing Data With Metadata

  • メタデータがデータとして格納されている点で前章のEAVに似ている
    • EAV: 属性名
    • Polymorphic Associations: 親テーブルの識別子
  • 次章 Multicolumn Attributesもこの類のアンチパターン

How to Recognize the Antipattern

  • こんなのか聞こえてきたら注意
    • 「このタグエンティティは、他の任意のエンティティに関連付けられます」
      • EAV同様。RDBにこの類の柔軟性はない
    • 「我らのDB設計ではFK制約は宣言できません」
      • FK制約はRDBの基本的な機能
      • それをすてるなんてとんでもない(意訳)
    • 「このentity_typeカラムとは何かって？こっちの別のカラムがどのテーブルを参照するかを示しているんだよ」
      • FKは全行同じテーブルを参照しなければならない
  • Polymorphic Associationsをサポートするフレームワークが存在する

Legitimate Uses of the Antipattern

  • フレームワークにより提供されている場合
    • FK制約を使えない代わりに、アプリケーションロジックで整合性を担保している

Solution: Simplify the Relationship

  • 複数の親テーブルを参照しないようにDB再設計する

Reverse the Reference

Creating Intersection Tables

Putting Up Traffic Lights

Looking Both Ways

Merging Lanes

Create a Common Super-Table

  • Class Table Inheritance
    • Issuesテーブルを作る
    • Bugs.issue_id, FeatureRequests.issue_idはPKかつFK
  • Comments.issue_idはIssues.issue_idを参照するようにする

英語

  • promiscuous
    • 誰とでも寝る
    • 文中では、複数の親テーブルを参照することの比喩として使われている