âš¡IntermediateJoins
One-to-Many and Many-to-Many Relationships
Design and query the two most common relationship types in relational databases.
Overview
A one-to-many relationship (e.g., one customer → many orders) is implemented with a foreign key in the "many" table referencing the "one" table's primary key. A many-to-many relationship (e.g., students ↔ courses) requires a junction table (also called a join or association table) that holds pairs of foreign keys from both tables.
Understanding these patterns is fundamental to relational database design. One-to-many is the most common relationship type. Many-to-many appears in tags, permissions, categories, and membership scenarios.
Key Points
- •One-to-many: FK in the "many" table pointing to the "one" table
- •Many-to-many: junction table with two FKs (PK is the composite of both)
- •Query one-to-many with JOIN; query many-to-many with two JOINs through junction
- •Add extra columns to the junction table to record relationship metadata
- •COUNT(orders.id) per customer is the canonical one-to-many aggregate pattern
Examples
One-to-many and many-to-many schema
-- One-to-many: one customer, many orders
CREATE TABLE customers (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT REFERENCES customers(id), total NUMERIC);
-- Many-to-many: students and courses
CREATE TABLE students (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE courses (id SERIAL PRIMARY KEY, title TEXT);
CREATE TABLE enrollments (
student_id INT REFERENCES students(id),
course_id INT REFERENCES courses(id),
enrolled_at DATE DEFAULT CURRENT_DATE,
PRIMARY KEY (student_id, course_id)
);Querying many-to-many
-- All courses a student is enrolled in
SELECT s.name AS student, c.title AS course
FROM students s
JOIN enrollments e ON e.student_id = s.id
JOIN courses c ON c.id = e.course_id
WHERE s.id = 1;
-- Students per course count
SELECT c.title, COUNT(e.student_id) AS students
FROM courses c
LEFT JOIN enrollments e ON e.course_id = c.id
GROUP BY c.title;Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4