One-to-Many and Many-to-Many

~14 min · schema, relationships

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Two relationship shapes that cover almost everything

Most data relationships boil down to two patterns:

One-to-many: one author writes many books; one customer places many orders. The "many" side holds a foreign key pointing to the "one" side.
Many-to-many: many students take many courses; many posts have many tags. Requires a junction table with two foreign keys.

One-to-one is just a one-to-many in disguise

If you genuinely need a one-to-one relationship, it's usually a one-to-many with a UNIQUE constraint on the foreign key. (Often the right answer is "don't split the table at all and just put the columns on the parent.")

Junction tables are first-class

The junction table connecting students and courses is not a hack — it's the correct shape. It can carry its own data: enrollment date, role, grade. The composite primary key (student_id, course_id) is the natural identity for the relationship row.

Code

One-to-many: authors and books·sql

CREATE TABLE authors (
    id   INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE books (
    id        INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER NOT NULL REFERENCES authors(id) ON DELETE RESTRICT
);

CREATE INDEX books_author_id_idx ON books (author_id);

Many-to-many with junction + extra data·sql

CREATE TABLE students (
    id   INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    id   INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE enrollments (
    student_id INTEGER NOT NULL REFERENCES students(id) ON DELETE CASCADE,
    course_id  INTEGER NOT NULL REFERENCES courses(id)  ON DELETE CASCADE,
    enrolled_on DATE NOT NULL DEFAULT CURRENT_DATE,
    grade      TEXT,
    PRIMARY KEY (student_id, course_id)
);

Querying through the junction·sql

SELECT c.name AS course
FROM   enrollments e
JOIN   students s ON s.id = e.student_id
JOIN   courses  c ON c.id = e.course_id
WHERE  s.name = 'Alice'
ORDER  BY c.name;

External links

Postgres Foreign Keys + Indexing

Exercise

Take a many-to-many relationship in your project (or invent one) and design the junction table — including any extra columns the relationship would benefit from. Explain why those extra columns belong on the junction and not on either of the two main tables.

Progress

Progress is local-only — sign in to sync across devices.

← PreviousForeign Keys and Relationships Next →UNIQUE, CHECK, and EXCLUDE Constraints

Spotted a bug or have feedback on this page?Report an Issue

Comments 0

🔔 Reply notifications (sign in)

No comments yet — be the first.