副管理者ができると 1:N が N:M になる
前回まで、モバイル招待状サービスの関係は 1:N だった。管理者一人が招待状をいくつも作り、招待状一つは自分を作った管理者一人だけを見ている。だから招待状テーブルには admin_id 列が一つあれば足りた。このセルには、管理者テーブルでその管理者の行を指す id が入る。
サービスがうまくいき、管理者も何人か採用して招待状も増えたとしよう。招待状を作った管理者しか直せないようにしていたので、その管理者に連絡がついて修正されるまでに時間がかかりすぎる。もっと早く対応できる副管理者が必要になる。
これで招待状一つを複数の管理者が受け持つ。管理者一人も相変わらず招待状を複数受け持つ。両側とも複数になるこの関係を多対多といい、N:M と書く。1:N では「複数」が片側にだけ付いていたが、N:M では両側に付く。
この連載で見た関係はこれで三つになる。基本となる 1:N、ときどき使う任意の 1:1、そして今回の N:M だ。
一つのセルにリストを入れない — 中間テーブルで解く
最初に思いつくのは、admin_id のセルに管理者を複数リストとして入れる方法だ。招待状 1 のセルに管理者 1、2、3 をまとめて書くやり方である。意味としては合っているが、テーブルはそういう使い方をしない。リレーショナルデータベースは、一つのセルに一つの値を入れることを基本に設計する。
代わりに関係を一行に一つずつ書く。招待状 1 と管理者 1、招待状 1 と管理者 2、招待状 1 と管理者 3、招待状 2 と管理者 4 というように。こうして関係だけを書くテーブルを別に一つ作る。講義の例では名前が「招待状管理」で、セルは自分の id と招待状 id、管理者 id だ。
すると N:M 一つが 1:N 二つに変わる。招待状一つに招待状管理の行が複数ぶら下がり、管理者一人にも招待状管理の行が複数ぶら下がる。前回見た 1:N を二回使うだけなので、新しく覚えるルールはない。
この中間テーブルには別の呼び名がある。講義ではジャンクションオブジェクト（junction object）と呼んでいた。二つのテーブルの間の関係を説明するために作ったオブジェクトという意味だ。ジャンクションテーブル、連結テーブルという名前も同じものを指す。
invitations admins invitation_admins
─────────── ────────── ──────────────────────────────
id groom_name id name id invitation_id admin_id
1 ミンス 1 ジウン 1 1 1
2 ジュノ 2 ソジュン 2 1 2
3 ハリン 3 1 3
4 ドユン 4 2 4
invitations 1 : N invitation_admins
admins 1 : N invitation_admins中間テーブルには役割を書ける
関係を別に切り出すとおまけが付く。中間テーブルに列を足せるのだ。関係そのものについての情報を書く場所ができたことになる。
講義の例は role（役割）列だ。招待状 1 について、管理者 1 は最初に作った人なので owner、残りは持ち主ではないが対応はできる人なので sub と書く。同じ管理者でも招待状ごとに役割が違いうるので、この値は管理者テーブルでも招待状テーブルでもなく、両者の間の行にあるのが正しい。
だから N:M に出会ったら、中間テーブルを一つ作って両側をそれぞれ 1:N でつなぐのが主な解決策になる。管理者と招待状の二つだけだった単純な ERD が、機能が付くたびにこうして広がっていく。
AI にこの構造を頼むときは、形を言葉で決めてあげればいい。下の依頼文のように中間テーブルの名前とセルを先に書いておけば、管理者リストを一つのセルに詰め込む答えが出る余地が減る。すでに溜まった招待状の admin_id の値を新しいテーブルに移す作業はマイグレーション、つまり既存データを新しい構造に移す変更なので、別途確認が要る。この部分は第 4 回で扱う。
| invitation_id | admin_id | role |
|---|---|---|
| 1 | 1 | owner |
| 1 | 2 | sub |
| 1 | 3 | sub |
| 2 | 4 | owner |
招待状一つを複数の管理者が管理できるように変えたい。
招待状テーブルの admin_id のセルに管理者リストを入れずに、
中間テーブル invitation_admins(id, invitation_id, admin_id, role) を作って
invitations と admins をそれぞれ 1:N でつないで。
role には owner か sub だけが入るようにして。
変える前に、既存の招待状データをどう移すかを先に説明して。結合 — 自分のテーブルにない情報を id で引いてくる
今度は招待状の一覧画面を作るとしよう。新郎・新婦といった招待状の情報と一緒に、その招待状がどのテンプレートを使っているか、そのテンプレートの URL とサムネイル（小さなプレビュー画像）も一目で見たい。
ところが招待状テーブルにはテンプレートの URL がない。前回テンプレートを別のテーブルに切り出したので、招待状の行には template_id しかなく、URL とサムネイルはテンプレートテーブルの行にある。
そこで招待状の行の template_id を持って、テンプレートテーブルで同じ id の行を探す。その行の URL を持ってきて、招待状の行の横に template_url という名前で付ける。自分のテーブルにはないが親テーブルにある情報を id で引いてきて付けること、これが結合（join）だ。
一覧画面を AI に任せるときは「テンプレートの URL とサムネイルはテンプレートテーブルから結合して持ってきて」と一行添えればいい。同じ値を招待状テーブルにもう一度コピーしておくやり方より、id 一つで引いてくる方が、テンプレートを直したときに値がずれない。
invitations templates
─────────────────────────────────────── ────────────────────────────────
id groom_name bride_name template_id id url thumbnail
1 ミンス ソヨン t-03 t-03 /t/spring spring.png
一覧画面に出る一行（結合の結果）
────────────────────────────────────────────────────────────
id groom_name bride_name template_url template_thumbnail
1 ミンス ソヨン /t/spring spring.pngMermaid で AI に ERD を描いてもらう
関係がここまで増えると、言葉だけで追うのは難しい。そこで使うのが Mermaid だ。名前は英語で人魚という意味で、文字で書いたコードを図に変えてくれる道具である。
AI と作業していると Markdown の文書をよく使うことになる。Markdown は # や - のような記号で見出しや箇条書きを書く文書形式で、README.md のように名前の末尾が .md のファイルがそれだ。この文書でバッククォート（`）三つでコードブロックを開いて mermaid と書き、次の行に erDiagram とテーブル・関係を書けば、Mermaid に対応した画面が ERD を自動で描いてくれる。
コードを自分で書く必要はない。講義が勧めるのは、AI に「Mermaid で ERD を描いて」と頼む方法だ。下は、この回に出てきた四つのテーブルをそうやって書いたコードである。||--o{ のような記号が第 2 回で見たカラスの足記法で、PK はその行を指す固有の id（主キー）、FK は別のテーブルの id を指すセル（外部キー）だ。
描かれた図を見ながら関係を一つずつ自分でなぞってみるのが、講義の言うコツだ。招待状と管理者の間に中間テーブルがあるか、一覧画面に必要な情報がどのテーブルから結合で来ているかを図で確かめる。次回は、こうして描いた構造を AI に任せたときに事故が起きる四つのことを、「一覧が遅い」「登録はできたのにポイントが入らない」といった症状から見分ける方法を見る。
このプロジェクトのいまのデータベース構造を Mermaid の erDiagram で描いて。
テーブルごとに列名とデータ型、PK・FK の印を入れて、
関係はカラスの足記法で 1:N か N:M かが見えるようにして。
N:M があれば、どの中間テーブルで解いたかも説明して。```mermaid
erDiagram
admins ||--o{ invitation_admins : manages
invitations ||--|{ invitation_admins : "managed by"
templates ||--o{ invitations : "used by"
admins {
uuid id PK
varchar name
}
invitations {
uuid id PK
varchar groom_name
varchar bride_name
uuid template_id FK
}
invitation_admins {
uuid id PK
uuid invitation_id FK
uuid admin_id FK
varchar role "owner or sub"
}
templates {
uuid id PK
varchar url
varchar thumbnail
}
```