3つ以上のテーブルの関連を表す時、交差テーブルはいくつ作るべきか

つまり、例えば以下のようなモデル構造の時

erDiagram

user {
  string id PK
  string name
}

tenant {
  string id PK
  string name
}

role {
  string id PK
  string name
}

「ユーザーは複数のテナントに所属できる」「ユーザーは複数のロールを割り当てられる」「あるテナントに所属するユーザーとそのロール」という要件について表したい時、交差テーブルは一つにするべきか、あるいは交差テーブルの交差テーブルを作ってでも分けるべきか。

先に結論を書いておくと、より細かい単位、というより責務毎で分ける、つまり交差テーブルの交差テーブルを作ってでも分けるべきだと思う。

まあモデリングに慣れている人だと「それはまあそうだろうな」という結論だと思う。

上記のようなテーブルが必要になるユースケースは、例えば「テナントの管理画面で所属メンバー一覧とそのロールを表示する」という、まあマルチテナントアプリケーションとかならありがちな物が考えられる。

個別に見ていこう。まずは交差テーブルを一つにしたパターン。

erDiagram

user {
  string id PK
  string name
}

tenant {
  string id PK
  string name
}

role {
  string id PK
  string name
}

tenant_membership {
  string id PK
  string tenant_id FK
  string user_id FK
  string role_id FK
}

user ||--o{ tenant_membership : ""
tenant ||--o{ tenant_membership : ""
role ||--o{ tenant_membership : ""

特徴を考えてみる。まずtenant_membershipを一意に決定するためには全てのデータが必要になる。全てのデータが揃ってようやく一意に特定できるのなら、テーブルを分けた場合、単純にレコード数が2倍以上になってしまうので、一見別にテーブルを分ける必要性はないのではないかと思えそうだが、実際には扱いづらいデータ構造になってしまっている。
tenant_membershipとroleを一旦分けて考えると分かり易いが、これらは多対多の関係になる。ということは交差テーブルで解決したい課題が浮き出るはず。例えばフィールドがNot Nullなのであれば、当然SELECT時もINSERT時も全てのデータが揃ってなければならない。この事前に作成できない状態というのは、まさに交差テーブルによって解決したい事象だ。
また単純に、命名から「ロールも紐付けられている」ということが分かりづらいのも問題だ。かといって愚直にロールにも関連しているということを示そうとするとぐちゃぐちゃな命名になりがちになってしまう。

この時点で交差テーブルを分けたほうが良さそうなことはわかるが、一応交差テーブルを分けたパターンの図も見てみよう

erDiagram

user {
  string id PK
  string name
}

tenant {
  string id PK
  string name
}

role {
  string id PK
  string name
}

tenant_membership {
  string id PK
  string tenant_id FK
  string user_id FK
}

tenant_role_assignment {
  string id PK
  string membership_id FK
  string role_id FK
}

user ||--o{ tenant_membership : ""
tenant ||--o{ tenant_membership : ""
tenant_membership ||--o{ tenant_role_assignment : ""
role ||--o{ tenant_role_assignment : ""

こうした場合、tenant_membershipはテナントとユーザーの情報があれば良い。先程の問題が解決できているし、「テナントの所属メンバー」という、名前通りのエンティティとして定義できているので混乱しない。
tenant_role_assignmentはJoinの手間こそ増えるものの、後からロールを紐付けることができるし、責務も「あるテナントメンバーに割り当てられたロール」として分けられる。

今回のパターンで実際に後から紐付けたいケースがあるかどうかはともかく、モデリングの時点で「不便になるかもしれない」を潰しておくことはとても重要で、後から別テーブルに切り出す場合はマイグレーションの手間もそれなりにかかってしまうので、なるべく未知の事象に対応できる状態にしておいた方が良い。
結局のところモデリングの基本として、情報の単位は最小限にする、2つの責務に分割できるのならばそうするのが適切だよね、という話に落ち着く問題だろう。