Relationships

A relational database splits data across many narrow tables (see Normalization). A relationship is how you reconnect them: a rule that says a row over here corresponds to one or more rows over there. Almost every schema question — “where does this column go?”, “do I need another table?” — comes down to identifying the relationship correctly.

The key property of a relationship is its cardinality: how many rows on each side are allowed to match. There are three shapes.

The building block: a foreign key

A foreign key (FK) is a column (or set of columns) in one table whose values must match a primary key in another table. It is the physical thing that implements a relationship. orders.customer_id holding the value 42 means “this order belongs to the customer whose customer_id is 42”. The database enforces that 42 actually exists in customers — you can’t point at a customer who isn’t there, and (with ON DELETE RESTRICT) you can’t delete a customer who still has orders.

Everything below is a question of which table the foreign key goes on, and whether it needs a UNIQUE constraint.

One-to-many (1:N)

The most common relationship by far. One row on the “one” side is referenced by any number of rows on the “many” side; each row on the many side points back to exactly one.

One customer has many orders. Each order was placed by exactly one customer.

Other everyday examples: an author and their books, a country and its cities, a blog post and its comments, a user and their sessions.

How it’s built

The foreign key lives on the many side.

CREATE TABLE customers (
  customer_id  BIGINT PRIMARY KEY,
  name         TEXT NOT NULL,
  email        TEXT NOT NULL
);

CREATE TABLE orders (
  order_id     BIGINT PRIMARY KEY,
  customer_id  BIGINT NOT NULL REFERENCES customers(customer_id),
  total        NUMERIC(10,2) NOT NULL,
  placed_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);

The customers table stores nothing about orders. It’s tempting to imagine a customers.order_ids column holding a list — resist it. A list in one column can’t be indexed, can’t be constrained by the database, and breaks the moment two orders need to reference the same customer. Storing the single customer_id on each order is the whole trick.

Querying it

-- Every order for one customer
SELECT order_id, total, placed_at
FROM orders
WHERE customer_id = 42;

-- Each customer with their order count (customers with zero orders included)
SELECT c.name, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name;

One-to-one (1:1)

A special case of one-to-many where the “many” side is capped at one. Each row on either side matches at most one row on the other.

One user has one profile. One profile belongs to one user.

How it’s built

Put the foreign key on either table, then add a UNIQUE constraint to it. That constraint is the entire difference from 1:N — it stops the “many” side from ever holding more than one row per parent.

CREATE TABLE profiles (
  profile_id  BIGINT PRIMARY KEY,
  user_id     BIGINT NOT NULL UNIQUE REFERENCES users(user_id),
  bio         TEXT,
  avatar_url  TEXT
);

When you’d actually use it

If the two tables are always one-to-one, why not merge them into a single table? Legitimate reasons to keep them apart:

• Splitting a wide table. Move rarely-read columns (a long bio, serialized settings) out of the hot table so the common queries scan less data.
• Isolating sensitive or differently-governed data. Payment details or PII in their own table with tighter access controls.
• Optional extension data. A subscriptions row that only exists for users who have ever subscribed — present for some rows, absent for most.

Many-to-many (M:N)

Both sides are unbounded. A row here can match many rows there, and vice versa.

One student enrols in many courses. One course has many students.

Other examples: products and orders (an order has many products; a product appears in many orders), tags and articles, actors and films, users and the groups they belong to.

Why neither table can hold the link

A relational database has no direct many-to-many. Every way of forcing it onto one of the two tables breaks:

• One FK column — students.course_id? It holds exactly one value, so a student could be in only one course. courses.student_id has the mirror problem.
• Several FK columns — course_id_1, course_id_2, course_id_3? You’ve hard-capped how many courses a student can take, wasted columns on students who take fewer, and turned “who is in course 205?” into a search across every numbered column.
• A list in one column — courses = '101,102,205'? The database can no longer check that those course IDs exist, can’t index them, and can’t join on them. That’s the 1NF violation from Normalization.

The junction table

The fix is a third table — the junction table (also called a join table, associative table, link table, or bridge table) — whose rows are the pairings themselves. One student in three courses is three rows. One course with 200 students is 200 rows. Nothing is capped, and each pairing is a real row you can constrain, index, and hang data off.

Here is the whole relationship as data. Two small parent tables:

students

courses

enrollments — the junction table, one row per student–course pairing:

Read it a row at a time: Ada (7) is in Databases and Compilers; Grace (8) is in Databases; Databases (101) has two students. The many-to-many lives entirely in this table — students and courses are never touched as enrolments come and go.

The same shape turns up everywhere once you learn to see it: order_items(order_id, product_id, quantity) pairs orders with products, role_permissions(role_id, permission_id) pairs roles with permissions, article_tags(article_id, tag_id) pairs articles with tags.

How it’s built

CREATE TABLE enrollments (
  student_id   BIGINT NOT NULL REFERENCES students(student_id),
  course_id    BIGINT NOT NULL REFERENCES courses(course_id),
  grade        TEXT,
  enrolled_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (student_id, course_id)
);

Three things are doing the work here:

1. Two foreign keys, one to each side. The junction table has a plain one-to-many relationship with students and another with courses — so a many-to-many is really just two one-to-many relationships back to back, meeting in the middle.
2. A composite primary key (student_id, course_id). It guarantees a given student–course pair appears at most once (no accidental double-enrolment) and, because student_id is the leading column, it also indexes “which courses does student 7 have?”.
3. Room for relationship attributes. grade and enrolled_at describe the pairing — not the student, not the course — so this is the only table where they can live.

Adding and removing pairings

Every relationship change is a one-row INSERT or DELETE on the junction table. The students and courses rows never change:

-- Ada enrols in Compilers
INSERT INTO enrollments (student_id, course_id) VALUES (7, 205);

-- Ada drops Databases
DELETE FROM enrollments
WHERE student_id = 7 AND course_id = 101;

-- A cancelled course: remove every enrolment for it
DELETE FROM enrollments WHERE course_id = 205;

Composite key or surrogate key?

The DDL above uses the natural composite key (student_id, course_id). It indexes lookups by student but not by course, so add the mirror index for the other direction:

CREATE INDEX ON enrollments (course_id, student_id);

Some teams instead give the junction table its own enrollment_id BIGINT PRIMARY KEY plus a separate UNIQUE (student_id, course_id) constraint. That’s worth doing when another table needs to point at a specific enrolment — say an enrollment_payments table — because a single-column foreign key is simpler to reference than a composite one.

Querying it

-- Courses one student is taking
SELECT co.title
FROM enrollments e
JOIN courses co ON co.course_id = e.course_id
WHERE e.student_id = 7;

-- Students in one course, with their grade
SELECT s.name, e.grade
FROM enrollments e
JOIN students s ON s.student_id = e.student_id
WHERE e.course_id = 101;

-- Enrolment count per course
SELECT co.title, COUNT(*) AS enrolled
FROM courses co
JOIN enrollments e ON e.course_id = co.course_id
GROUP BY co.course_id, co.title;

Recognising the cardinality

Before writing any DDL, ask both directions explicitly and answer yes or no:

Two “no” answers → one-to-one. One “yes” → one-to-many, and the FK goes on the side that answered “yes” (the “many” side). Two “yes” answers → many-to-many, add a junction table.