LoginSignup
34
31

More than 5 years have passed since last update.

@suguru

6章 ポリモーフィック関連

Posted at

バグに対してコメントを書き込める機能
1対多の関連が存在するテーブルを作成するSQL

# Polymorphic/intro/comments.sql
CREATE TABLE Comments (
  comment_id   SERIAL PRIMARY KEY,
  bug_id       BIGINT UNSIGNED NOT NULL,
  author       BIGINT UNSIGNED NOT NULL,
  comment_date DATETIME NOT NULL,
  comment      TEXT NOT NULL,
  FOREIGN KEY (author) REFERENCES Account(account_id),
  FOREIGN KEY (bug_id) REFERENCES Account(bug_id)
);

コメントが記入できるテーブルが2つ(Bugs, FeatureRequests)あったとき
複数の親テーブルを参照する外部キーを宣言することはできない

複数のテーブルへのクエリ実行しようとした無効なSQL
sql
SELECT c.*, i.summary, i.status
FROM Comments AS c
INNER JOIN c.issue_type AS i USING (issue_id);

SQL構文では、クエリの実行中に、テーブルは変更できない

6.1 目的：複数の親テーブルを参照する

6.2 アンチパターン：二重目的の外部キーを使用する

これを解決する方法の1つがポリモーフィック関連(Polymorphic Associations)

6.2.1 ポリモーフィック関連を定義する

  • 文字列型の列を追加する
  • 追加した列には、現在の行が参照する親テーブルの名前を格納する
# Polymorphic/anti/comments.sql
CREATE TABLE Comments (
  comment_id   SERIAL PRIMARY KEY,
  issue_type   VARCHAR(20), -- 'Bugs' または 'FeatureRequests' が格納される
  issue_id     BIGINT UNSIGNED NOT NULL,
  author       BIGINT UNSIGNED NOT NULL,
  comment_date DATETIME NOT NULL,
  comment      TEXT NOT NULL,
  FOREIGN KEY (author) REFERENCES Account(account_id)
);
  • issue_idのための外部キー宣言がなくなる
  • Comments.issue_idの値が親テーブルの値と一致することを保証するための参照整合性制約を定義できない
  • Comments.issue_type内の文字列データが、データベースに存在するテーブル名に対応していることも保証できない

6.2.2 ポリモーフィック関連へのクエリ実行

  • SQLでJOINを行うにはあらかじめすべてのテーブルを指定しなければならない
  • 両方の親テーブルを外部結合 (OUTER JOIN) する
SELECT *
FROM Comments AS c
  LEFT OUTER JOIN Bugs AS b
    ON b.issue_id = c.issue_id AND c.issue_type = 'Bugs'
  LEFT OUTER JOIN FeatureRequests AS 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 クラッシュします 1234 NULL
9876 FeatureRequests 2345 いいアイデア！ NULL 2345

6.2.3 非オブジェクト指向の例

  • ポリモーフィック関連は、親テーブル同士にまったく関係が無い場合にも使われる
  • 例 Eコマース向けのデータベース 顧客(Users)と注文(Orders)の2つのテーブルは、住所(Addresses)と関連づけられる
  • Addresses.parrentはどちらか1つを選択しなければならないので、注文商品の出荷先が顧客自身の住所であっても、両方に同じ住所の行を関連づけることはできない
CREATE TABLE Addresses (
  address_id SERIAL PRIMARY KEY,
  parrent    VARCHAR(20), -- 'Users' または 'Orders' が入る
  parent_id  BIGINT UNSINGED NOT NULL,
  address    TEXT
);
  • 顧客が出荷先だけでなく、請求先住所を持つ場合、Addressesテーブルでそれを区別する方法が必要
CREATE TABLE Addresses (
  address_id   SERIAL PRIMARY KEY,
  parent       VARCHAR(20), -- 'Users' または 'Orders' が入る
  parent_id    BIGINT UNSIGNED NOT NULL,
  users_usage  VARCHAR(20), -- 'billing' または 'shipping' が入る
  orders_usage VARCHAR(20), -- 'billing' または 'shipping' が入る
  address      TEXT
);

6.3 アンチパターンの見つけ方

アンチパターンが使われている可能性がある言葉

  • タグでデータベース内の「あらゆる」リソースと関連づけるようなスキーマが存在する
  • 外部キーが宣言できない
  • ある行が参照すべきテーブルを示すentity_type列が存在する

フレームワークによってはポリモーフィック関連をサポートしているものもある

6.4 アンチパターンを用いてもよい場合

なるべくポリモーフィック関連の使用は避け、外部キー制約などを用いて、参照整合性を保証すべき

=> アプリケーションコードに依存してしまう

アンチパターンを使わざるを得ない場合

  • HibernateなどのORMフレームワークを用いる場合 ### アンチパターンを用いてもいい場合
  • 成熟した実績のあるフレームワークを使う場合

6.4.1 ポリモーフィック関連を意識的に選択するとき

ORMを使った設計を行う際に、子側のテーブルを設計・作成する時点ではまだ存在しない親テーブルとの関連を作成できること
* 子側のテーブルと親側のテーブルの作成時期が離れているとき
* 最終的にどのような親テーブルといくつ関連を持つかわからないとき

リスクを念頭におきつつ設計の１つとして検討することもある
しかし、多くの場合は設計を再検討するほうがよい

6.5 解決策：関連(リレーションシップ)を単純化する

6.5.1 参照を逆にする

  • ポリモーフィック関連 => 本来あるべき関連が、逆さまになっている

6.5.2 校差テーブルの作成

  • 複数の親テーブルにそれぞれに対応した交差テーブルを作成する
  • 各交差テーブルではCommentsへの外部キーに加えて、各親テーブルへも同じく外部キーを定義する
CREATE TABLE BugsComments (
  issue_id       BIGINT UNSIGNED NOT NULL,
  comment_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (issue_id, comment_id),
  FOREIGN KEY (issue_id) REFERENCES Bugs(issue_id),
  FOREIGN KEY (comment_id) REFERENCES Comments(comment_id) 
);


CREATE TABLE FeaturesComments (
  issue_id       BIGINT UNSIGNED NOT NULL,
  comment_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (issue_id, comment_id),
  FOREIGN KEY (issue_id) REFERENCES FeatuersRequests(issue_id),
  FOREIGN KEY (comment_id) REFERENCES Comments(comment_id) 
);
  • Commentss.issue_type列が不要になる
  • メタデータがデータの整合性を保証してくれるので、アプリケーションコードに依存せず、間違いの生じる余地が少なくなる

6.5.3 交差点に交通信号を設置する

  • 許可したくない関連づけが許可されてしまう可能性がある => 各交差テーブルのcomment_id列にUNIQUE制約を宣言する
# Polymorphic/soln/reverse-unique.sql

CREATE TABLE BugsComments (
  issue_id       BIGINT UNSIGNED NOT NULL,
  comment_id BIGINT UNSIGNED NOT NULL,
  UNIQUE KEY (comment_id),
  PRIMARY KEY (issue_id, comment_id),
  FOREIGN KEY (issue_id) REFERENCES Bugs(issue_id),
  FOREIGN KEY (comment_id) REFERENCES Comments(comment_id) 
);
  • UNIQUE制約をつけても、あるコメントがBugsとFeatureRequestsの両方に関連づけられてしまうことを防げない => アプリケーションコードで対処するしかない

6.5.4 両方の「道」を見る

交差テーブルを用いると、特定のバグまたは機能要求を指定したコメントへのクエリを実行できる

SELECT *
FROM BugsComments AS b
  INNER JOIN Comments AS c USING (comment_id)
WHERE b.issue_id = 1234;

また、両方の交差テーブルと外部結合を行うことで、あるコメントに関連づいているバグまたは機能要求を取得できる

SELECT *
FROM Comments AS c
  LEFT OUTER JOIN (
    BugsComments INNER JOIN Bugs AS b
      USING (issue_id)
  ) USING (comment_id)
  LEFT OUTER JOIN (
    FeaturesComments INNER JOIN FeatureRequests AS f
      USING (issue_id)
  ) USING (comment_id)
WHERE c.comment_id = 9876;

6.5.5 「道」を合流させる

複数の親テーブルに対するクエリの結果を、親テーブルたちがあたかも1つのテーブルに格納しているかのように扱いたい場合、2つの方法がある

  1. UNIONを用いる
# Polymorphic/soln/reverse-union.sql
SELECT b.issue_id, b.description, b.reporter, b.priority, b.status,
    b.severity, b.version_affected,
    NULL AS sponsor
  FROM Comments AS c
    INNER JOIN (
      BugsComments INNER JOIN Bugs AS b
        USING (issue_id)
    ) USING (comment_id)
  WHERE c.comment_id = 9876
UNION
  SELECT f.issue_id, f.description, f.reporter, f.priority, f.status,
    NULL AS severity, NULL AS version_affected,
    f.sponsor
  FROM Comments AS c
    INNER JOIN (
      FeaturesComments INNER JOIN FeatureRequests AS b
        USING (issue_id)
    ) USING (comment_id)
  WHERE c.comment_id = 9876
  • UNIONで統合できるのは、列の数とデータ型が同じである場合のみのため、UNIONされる両方のクエリで同じ順番で列を列挙する必要がある
  1. SQLのCOALESCE関数を用いる
# Polymorphic/soln/reverse-coalesce.sql
SELECT c.*,
  COALESCE(b.issue_id,    f.issue_id)    AS issue_id,
  COALESCE(b.description, f.description) AS description,
  COALESCE(b.reporter,    f.reporter)    AS reporter,
  COALESCE(b.priority,    f.priority)    AS priority,
  COALESCE(b.status,      f.status)      AS status,
  b.severity,
  b.version_affected,
  f.sponsor
FROM Comments AS c
  LEFT OUTER JOIN (
    BugsComments INNER JOIN Bugs AS b
      USING (issue_id)
  ) USING (comment_id)
  LEFT OUTER JOIN (
    FeaturesComments INNER JOIN FeatureRequests AS f
      USING (issue_id)
  ) USING (comment_id)
WHERE c.comment_id = 9876;

6.5.6 共通の親テーブルの作成

  • オブジェクト指向のポリモーフィック
    => 2つのサブタイプを同じように参照できる

  • SQLの世界のポリモーフィック関連アンチパターン
    => 共通の基底型が抜け落ちている
    => 全ての親テーブルが継承する規定テーブルを作成することで対応可能

# Polymorphic/soln/super-table.sql
CREATE TABLE Issues (
 issue_id SERIAL PRIMARY KEY
 ・・・
);

CREATE TABLE Bugs (
  issue_id BIGINT UNSIGNED PRIMARY KEY,
  FOREIGN KEY (issue_id) REFERENCES Issues(issue_id),
  ・・・
);

CREATE TABLE FeatureRequests (
  issue_id BIGINT UNSIGNED PRIMARY KEY,
  FOREIGN KEY (issue_id) REFERENCES Issues(issue_id),
  ・・・
);

CREATE TABLE Comments (
  comment_id   SERIAL PRIMARY KEY,
  issue_id     BIGINT UNSIGNED NOT NULL,
  author       BIGINT UNSIGNED NOT NULL,
  comment_date DATETIME NOT NULL,
  comment      TEXT,
  FOREIGN KEY (issue_id) REFERENCES Issues(issue_id),
  FOREIGN KEY (author) REFERENCES Account(account_id)
);
34
31
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
34
31