Twitterのデータベース設計の課題を作成するにあたり、中間テーブルについて学んだので、自分なりにまとめた。
中間テーブルを理解する前に
中間テーブルを理解するためには、テーブルのリレーションシップや、ER図について理解しておく必要がある。 以下の記事の説明が分かりやすい。
また、この記事に出てくる、テーブル,エンティティ,レコードなどの用語については以下の記事に書いている。
リレーションシップは4種類だけ
テーブルのリレーションシップは4種類のみである。
1対11対多多対1多対多
一番最後の多対多の関係になっているテーブルは、そのままでは結合することができない。
これを解決する方法の一つとして、2つのテーブルの間に「中間テーブル」というテーブルを配置する、というやり方がある。
そもそも多対多とは、どういう関係か?
中間テーブルについて説明する前に、そもそも多対多がどういう関係なのかを先に説明する。
学生とサークルの関係を例に考える。
✅ 学生から見た、サークルとの関係
学生一人が所属できるサークルの数は、複数である。そのため、学生から見たサークルとの関係は、学生:サークル=1:多である。
(学生はサークルには絶対に所属しなくてもいいため、厳密には1対0~多の関係)
✅ サークルから見た、学生との関係
一つのサークルに対して、複数人の学生が所属することができる。そのため、サークルから見た学生との関係は、サークル:学生=1:多である。
(学生が一人も所属していない、0人のサークルが存在する可能性があるため、厳密には1対0~多の関係と考える)
✅ 学生とサークルの関係は、多対多
以上のことから、学生:サークル=多対多の関係であることが分かる。
多対多の関係のテーブル同士を結合したい
✅ テーブルを作ってレコードを入れてみる
2つのテーブルを作り、レコード(実際のデータそのもの)を入れてみると、以下のようになる。
【学生テーブル】
| 学生ID(主キー) | 学生の名前 |
|---|---|
| 1 | 山田花子 |
| 2 | 佐藤太郎 |
| 3 | 田中桜子 |
【サークルテーブル】
| サークルID(主キー) | サークル名 |
|---|---|
| 1 | 放送研究会 |
| 2 | スポーツ新聞部 |
| 3 | 英語部 |
| 4 | 映画研究会 |
| 5 | 野球部 |
ここで、
- 学生が何のサークルに所属しているか
- どのサークルにどの学生が所属しているか
をデータとして管理するデータベース設計をしたい場合、学生とサークルの2つのエンティティにリレーションシップを設定する必要がある。
しかし、
リレーショナルデータベースの「お約束」として、この多対多の関連は作ってはならない
(『達人に学ぶDB設計 徹底指南書 初級者で終わりたくないあなたへ』(ミック 著,翔泳社,2012年出版)の128ページより)
とあるため、多対多の関係のテーブルはそのまま結合することができない。
そこで、中間テーブルが必要になる。
✅ 2つのテーブルの間に中間テーブルを配置する
2つのテーブルの間に、中間テーブルを配置すると、以下のようになる。
中間テーブルを作る時は、接続先のテーブルそれぞれの主キーを、外部キーとして持たせる。
今回の例でいうと、学生_サークルテーブルには、学生テーブルの主キー「学生ID」と、サークルテーブルの主キー「サークルID」を、外部キーとして持たせている。
これによって、学生エンティティとサークルエンティティに、リレーションシップを設定することができる。
✅ レコードを登録してみる
実際にレコードを3つのテーブルに登録してみると、分かりやすい。
【学生テーブル】
| 学生ID(主キー) | 学生の名前 |
|---|---|
| 1 | 山田花子 |
| 2 | 佐藤太郎 |
| 3 | 田中桜子 |
【サークルテーブル】
| サークルID(主キー) | サークル名 |
|---|---|
| 1 | 放送研究会 |
| 2 | スポーツ新聞部 |
| 3 | 英語部 |
| 4 | 映画研究会 |
| 5 | 野球部 |
【学生_サークルテーブル】
| 学生_サークルID(主キー) | 学生ID(外部キー) | サークルID(外部キー) |
|---|---|---|
| 1 | 1 | 3 |
| 2 | 1 | 5 |
| 3 | 2 | 2 |
| 4 | 3 | 2 |
| 5 | 3 | 1 |
| 6 | 3 | 4 |
中間テーブルを配置すると、
- 学生が何のサークルに所属しているか
- どのサークルにどの学生が所属しているか
というデータを抜き出せるようになる。
この例でいうと、
- 学生のデータを抜き出したい場合
- 山田花子は英語部と野球部に所属している
- 佐藤太郎はスポーツ新聞部に所属している
- サークルのデータを抜き出したい場合
- 放送研究会に所属している学生は、田中桜子
- スポーツ新聞部に所属している学生は、佐藤太郎と田中桜子
ということが分かる。
中間テーブルの特徴まとめ
- 多対多の関係のテーブルを結合したい時の解決法の一つ
(状況によっては、中間テーブルを作った方がいいケースばかりではない) - 2つのテーブルの間に配置する
- 2つのテーブルそれぞれの主キーを、外部キーとして持つ
