1対多および多対多リレーションシップ

リレーショナルデータベース設計において、テーブル間のリレーションシップをどのように表現するかを理解することは重要です。最も一般的なリレーションシップの種類には、1対多 と 多対多 があります。それぞれの種類は、あるテーブルのレコードが別のテーブルのレコードとどのように関連するかを示しており、SQLでのモデリング方法がデータのクエリや保守に直接影響します。

1対多リレーションシップ とは、1つのテーブルの単一レコードが、別のテーブルの複数のレコードと関連できる一方で、2つ目のテーブルの各レコードは1つ目のテーブルの1つのレコードのみに関連することを意味します。例えば、courses と enrollments のシナリオを考えてみましょう。各コースは複数の受講登録を持つことができますが、各受講登録は正確に1つのコースに対応します。

-- Example: One-to-many relationship between courses and enrollments
CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100)
);

CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY,
    course_id INT,
    student_id INT,
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
-- Here, each course can have many enrollments, but each enrollment refers to one course.

多対多リレーションシップ を表現する必要がある場合は、少し複雑になります。この場合、1つのテーブルのレコードが別のテーブルの複数のレコードと関連し、逆もまた同様です。例えば、学生は複数のコースを受講でき、各コースには多くの学生が登録できます。リレーショナルデータベースでは、2つのテーブル間で直接的な多対多リレーションシップをサポートしていません。そのため、このパターンは 中間テーブル（アソシエーションテーブルやリンクテーブルとも呼ばれます）を使ってモデリングします。中間テーブルは、両方の関連テーブルの主キーを参照する外部キーを保持し、多対多リレーションシップを2つの1対多リレーションシップに分割します。

-- Modeling a many-to-many relationship between students and courses using enrollments as a junction table
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50)
);

CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100)
);

CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
-- The enrollments table links students and courses, allowing each student to enroll in multiple courses and each course to have multiple students.