1対1/1対多/多対多の設計
外部キーを、どちらの表に置いても足りない
学生と講座を結びます。1 人の学生は複数の講座を履修し、1 つの講座には複数の学生が集まります。
students に course_id を置いてみます。1 人が 1 講座しか取れません。では courses に student_id を置きます。今度は 1 講座に 1 人しか入れません。
SQL クエリ
-- どちらも業務を表せない
CREATE TABLE students (
id INT PRIMARY KEY,
name TEXT NOT NULL,
course_id INT -- 2 つ目の講座が入らない
);1 対多なら「多の側に置く」で決着しました。多対多には多の側が 2 つあるので、置き場所が無いのです。
関連そのものを、1 行にする
置き場所が無いなら、置くための表を作ります。「誰がどの講座を取ったか」という事実を 1 行として扱う表です。中間テーブルと呼びます。
SQL クエリ
CREATE TABLE enrollments (
student_id INT NOT NULL,
course_id INT NOT NULL,
PRIMARY KEY (student_id, course_id)
);学生と講座の組み合わせを 1 行ずつ入れていきます。1 人が 3 講座取るなら 3 行、1 講座に 40 人いるなら 40 行です。両側とも何件でも増やせます。
主キーを (student_id, course_id) の組にしているのが要点です。同じ学生が同じ講座に二重登録されると、履修者数が 1 人多く数えられ、成績も 2 行に分かれます。組を主キーにしておけば、二重登録は INSERT の時点で弾かれます。id 列だけを主キーにすると、この保証は消えます。
関連に属性が付いたら、それは独立した表になっている
履修には、成績や履修日が付きます。これは学生の属性でも講座の属性でもなく、「その学生がその講座を取ったこと」の属性です。中間テーブルは、その置き場所になります。
SQL クエリ
CREATE TABLE enrollments (
student_id INT NOT NULL,
course_id INT NOT NULL,
enrolled_at DATE NOT NULL,
grade TEXT,
PRIMARY KEY (student_id, course_id)
);ここまで来ると、中間テーブルは単なる連結役ではなく、履修という 1 つのエンティティです。名前も student_courses のような連結名ではなく、業務で使われている enrollments のような名詞を付けたほうが、後で読む人に伝わります。
なお 1 対 1 は、実務では出番が多くありません。ほとんどの場合は同じ表にまとめられるからです。あえて分けるのは、本文のような大きな列を検索用の表から切り離したいときや、機密情報を別の権限で守りたいときに限られます。
テーブル構造
CREATE TABLE students (
id INT PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE courses (
id INT PRIMARY KEY,
title TEXT NOT NULL
);
CREATE TABLE enrollments (
student_id INT NOT NULL,
course_id INT NOT NULL,
enrolled_at DATE NOT NULL,
grade TEXT,
PRIMARY KEY (student_id, course_id)
);
INSERT INTO students (id, name) VALUES
(1, '田中'),
(2, '鈴木'),
(3, '佐藤');
INSERT INTO courses (id, title) VALUES
(1, 'データベース基礎'),
(2, 'アルゴリズム'),
(3, 'Web開発');
INSERT INTO enrollments (student_id, course_id, enrolled_at, grade) VALUES
(1, 1, '2026-04-01', 'A'),
(1, 2, '2026-04-02', 'B'),
(1, 3, '2026-04-03', 'A'),
(2, 1, '2026-04-01', 'B'),
(2, 2, '2026-04-02', 'C'),
(3, 3, '2026-04-03', NULL);期待される出力
| student_name | course_title | grade |
|---|---|---|
| 田中 | データベース基礎 | A |
| 田中 | アルゴリズム | B |
| 田中 | Web開発 | A |
| 鈴木 | データベース基礎 | B |
| 鈴木 | アルゴリズム | C |
| 佐藤 | Web開発 | NULL |