Understanding how to represent relationships between tables is essential in relational database design. Two of the most common relationship types are one-to-many and many-to-many. Each type describes how records in one table relate to records in another, and the way you model them in SQL directly affects how you query and maintain your data.

A one-to-many relationship means that a single record in one table can be associated with multiple records in another table, but each record in the second table relates back to only one record in the first. For instance, consider a scenario with courses and enrollments: each course can have many enrollments, but each enrollment is for exactly one course.

-- 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.

When you need to represent a many-to-many relationship, things become a bit more complex. In this case, a record in one table can relate to multiple records in another, and vice versa. For example, students can enroll in many courses, and each course can have many students enrolled. Relational databases do not support direct many-to-many relationships between two tables. Instead, you model this pattern using a junction table (also known as an association or linking table). The junction table holds foreign keys referencing the primary keys of both related tables, effectively breaking the many-to-many relationship into two one-to-many relationships.

-- 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.