複数のテーブルに多対1で紐づく時のテーブル設計のアプローチについて

ポリモーフィック SQLアンチパターン

データベース

2018-08-07 11:36:36

概要

複数のテーブルに対し、多対1でテーブルが関係付くときのテーブル設計のパターンについてまとめる。

データ設計

以下のようなケースのデータ設計を例とする。 

  • issues

    • id
    • title
  • pullrequests

    • id
    • title
  • comments

    • id
    • content

commentsissuespullrequestsのどちらに対しても多対一で関係付くようなケース。

ポリモーフィック関連

  • issues

    • id
    • title
  • pullrequests

    • id
    • title
  • comments

    • id
    • content
    • target_table
    • target_id

commentstarget_tabletarget_idというカラムを追加し、issuespullrequestsのどちらに結びつくか判断させようとするテーブル設計。

SQLアンチパターンではアンチパターンの一つとして取り上げられている。

target_idtarget_tableを見ないとissuespullrequestsのどちらに関連付くかわからないため、外部キー制約が使えない
したがって、このパターンではテーブル間の整合性保持はアプリケーションのロジックに依存することなる。

LaravelやRailsのORMではポリモーフィック関連がサポートされているので実装が楽なので、このようなパターンを検討する余地はゼロではないが、なるべく避けたいパターンではある。

交差(ピボット、中間)テーブル

  • issues

    • id
    • title
  • pullrequests

    • id
    • title
  • issues_comments

    • issues_id
    • comments_id
  • pullrequests_comments

    • pullrequests_id
    • comments_id
  • comments

    • id
    • content

issuespullrequestsに交差テーブルを用意して、外部キー制約を使えるようにするパターン。

issuesissues_commentsは1対多、issues_commentscommentsは多対1となる。pullrequestsに関しても同様。

アプリケーションの要件次第ではあるが、1コメントがissuespullrequestsのどちらかだけに関連付くようにという制約を保証できない。

外部キーが使えるため、ポリモーフィック関連よりは整合性を保つことができる。 

共通の親を持つテーブル

  • issues

    • id
    • post_id
  • pullrequests

    • id
    • post_id
  • posts

    • id
    • title
  • comments

    • id
    • content
    • post_id

issuespullrequestscommentsの共通の親となるテーブルを用意するパターン。 

postsはクラステーブル継承の考え方に基づいて定義するのが良さそう。(要は基底クラスと考える)
(参考:単一テーブル継承・クラステーブル継承・具象クラス継承について
PofEAA

issuespostsが1対1、postscommentsが1対多で関連付く。pull_requestsも同様。
postscommentsは1対多で関連付く。

1コメントは1postsに関連付くという制約を保証できるが、issuespullrequests のどちらかだけに関連付くという制約を保証できない。

テーブル分割

  • issues

    • id
    • title
  • pullrequests

    • id
    • title
  • issue_comments

    • id
    • issues_id
    • content
  • pullrequest_comments

    • id
    • pullrequests_id
    • content

これはそもそもの前提を疑う話ではあるが、comments を1つのテーブルにまとめておくのではなく、別々のcommentsテーブルをそれぞれ用意してテーブルを分割しておけば良いのではないかというパターンである。

所感

アプリケーション側のロジックに依存することは、ヒューマンエラーの可能性を高めるので、テーブル構造にロジックを依存させる設計方針が基本的には良いパターンではないかと思う。
アプリケーションの要件に加えて、クエリの気持ちを考えて最適なパターンを選択できるようにしたい。