正規化のためのテーブル間リレーションのモデリング

今日はめっちゃ大事なテーマに深く突っ込んでいくよ：テーブル間リレーションのモデリングについて。
正規化って、ただデータを細かく分けたり冗長性をなくすだけじゃなくて、テーブル同士の正しいリレーションを作ることも大事なんだ。

データベースが情報を整理して保存するシステムなら、テーブル間のリレーションはデータ同士がどう関わるかを示す論理的な橋みたいなもの。
例えば図書館を想像してみて。本の情報は著者の情報と別々に保存されてるけど、各本は特別なリレーションを通じて自分の著者を「知ってる」んだ。
ネットショップも同じ。商品データは購入者情報と独立してるけど、誰かが注文したら、注文テーブルを通じて特定の購入者と商品がつながる。

医療クリニックなら、患者は自分のカルテとつながってるし、医者は診察スケジュールと、薬は処方とリレーションしてる。こういうリレーションがあるから、システムはどの情報がどれに関係してるかを理解できるし、無駄なデータの重複も防げるんだ。

こういうリレーションの基本的なタイプは、リアルな人間関係みたいなもんだよ：パスポートは一人だけのもの（1対1）、一人の先生が複数のコースを担当できる（1対多）、学生は色んな科目を取れるし、各科目には色んな学生がいる（多対多）。

1対1（1:1）

これは「A」テーブルの1レコードが「B」テーブルの1レコードだけに対応するリレーション。
例えば「従業員」と「パスポート情報」テーブルがあるとする。
1人の従業員は1つだけパスポートを持てて、各パスポートは唯一の従業員に属してる。

例：

従業員

パスポート情報

ここでは従業員_idという外部キーで「従業員」テーブルのidを参照してリレーションを作ってるよ。

1対多（1:N）

これが一番よく使うリレーションタイプ。「A」テーブルの各レコードが「B」テーブルの複数レコードとつながるけど、「B」テーブルの各レコードは「A」テーブルの1レコードだけとつながる。
例えば「先生」と「コース」テーブルがあるとする。1人の先生が複数のコースを担当できる。

例：

先生

コース

「コース」テーブルの先生_id外部キーでリレーションを作ってるよ。

多対多（M:N）

たくさんあると楽しいけど、ちょっとややこしいやつ。
「A」テーブルの各レコードが「B」テーブルの複数レコードとつながるし、その逆もOK。
例えば学生は複数のコースを取れるし、各コースには複数の学生がいる。

例：

学生

コース

このリレーションを作るには、中間テーブルが必要。学生とコースの対応を保存するやつ：

登録

外部キーでリレーションをモデリングする

外部キーは、他のテーブルの主キーを参照するカラム（またはカラムのセット）。
これがテーブル間リレーションの基本になる。

外部キーの例：

CREATE TABLE コース (
    id SERIAL PRIMARY KEY,
    名前 VARCHAR(255)
);

CREATE TABLE 登録 (
    id SERIAL PRIMARY KEY,
    学生_id INT,
    コース_id INT,
    FOREIGN KEY (コース_id) REFERENCES コース(id)
);

外部キー設計でミスしないコツは？
まず、外部キーと主キーのカラム型が一致してるか確認しよう。違うとDBがリレーションを作らせてくれない。
あと、親テーブルのレコードを消したときに子テーブルのデータをどうするかも考えておこう。
例えば親テーブルの行を消したら、子テーブルの関連データも一緒に消したい場合はON DELETE CASCADEを使うのが定番。
これで「ぶら下がり」データが残らず、DBの整合性も保てるよ。

多対多リレーションの実装

例として、学生とコースがあるとしよう。
1人の学生が複数のコースを取れて、1つのコースにも複数の学生が登録できる。
M:Nリレーションを作るには、学生、コース、登録の3テーブルを作る。

CREATE TABLE 学生 (
    id SERIAL PRIMARY KEY,
    名前 VARCHAR(255)
);

CREATE TABLE コース (
    id SERIAL PRIMARY KEY,
    名前 VARCHAR(255)
);

CREATE TABLE 登録 (
    id SERIAL PRIMARY KEY,
    学生_id INT,
    コース_id INT,
    FOREIGN KEY (学生_id) REFERENCES 学生(id),
    FOREIGN KEY (コース_id) REFERENCES コース(id)
);

これで登録テーブルにデータを入れれば、学生とコースをつなげられる。

実践課題

コース管理システム用のDB構造を作ってみよう。学生、コース、登録テーブルが必要。
全部のリレーションを実装して、サンプルデータも入れてみてね。
やり方を見ていこう。

1. テーブルを作成：

CREATE TABLE 学生 (
    id SERIAL PRIMARY KEY,
    名前 VARCHAR(255)
);

CREATE TABLE コース (
    id SERIAL PRIMARY KEY,
    名前 VARCHAR(255)
);

CREATE TABLE 登録 (
    id SERIAL PRIMARY KEY,
    学生_id INT,
    コース_id INT,
    FOREIGN KEY (学生_id) REFERENCES 学生(id),
    FOREIGN KEY (コース_id) REFERENCES コース(id)
);

2. データを挿入：

INSERT INTO 学生 (名前) VALUES ('オットー・リン'), ('マリア・チ');
INSERT INTO コース (名前) VALUES ('SQLの基礎'), ('DB管理');
INSERT INTO 登録 (学生_id, コース_id) VALUES (1, 1), (1, 2), (2, 1);

3. データを確認：

SELECT
    学生.名前 AS 学生, 
    コース.名前 AS コース
FROM 登録
JOIN 学生 ON 登録.学生_id = 学生.id
JOIN コース ON 登録.コース_id = コース.id;

結果：

リレーションモデリングの難しさとポイント

テーブル間リレーションをモデリングするとき、こんな難しさがあるよ：

• データ削除時のエラー（例えば親テーブルのレコードに依存する子テーブルのデータがある場合）
• 大量データ時のクエリパフォーマンス。M:Nリレーションは特に「重い」から、結合が増える。

こういう問題は、

• 外部キーにインデックスを貼る
• よく考えたDB構造にする
• 正規化とパフォーマンスのバランスを取る

で解決できるよ。
今回はめっちゃ基本的なレベルでテーブル間リレーションのモデリングをやってみたし、実際にコース管理システムのDB構造も作ってみた。
本当はもっと大きな例をやりたいけど、どうやってやるか思いつかなかった。大きい例はややこしくてつまらなくなりがちだし、あんまり意味ないかも。
この話はコースの終わりごろにまた戻ってくるつもり！