Relationships, Cardinality and Optionality

In one line

Relationships connect entities: one-to-one, one-to-many and many-to-many, each with a cardinality (how many) and optionality (must there be one?). One-to-many is a foreign key on the "many" side; many-to-many needs an associative table; self-referencing relationships model hierarchies and graphs.

0/4 · 0%

Think of it like this

A school. One teacher teaches many classes (one-to-many), each student enrols in many courses and each course has many students (many-to-many via an enrolment), and each teacher may have a mentor who is also a teacher (self-referencing).

Key ideas

01
One-to-many: put the FK on the many side (order.customer_id). Optionality: NOT NULL if every order must have a customer; nullable if the relationship is optional.
02
One-to-one: an FK with a UNIQUE constraint (user_profile.user_id UNIQUE), or share the primary key. Use it to split rarely read or sensitive columns, or optional extensions; otherwise just add columns to one table.
03
Many-to-many: an associative (junction) table with both FKs and a composite primary key (enrolment(student_id, course_id)). When the relationship has its own attributes (grade, enrolled_at), it becomes an associative entity in its own right.
04
Self-referencing / recursive: employee.manager_id REFERENCES employee(id) models a tree. Query it with a recursive CTE (Phase 4.4). For graphs (friends), use a relationship table friendship(user_a, user_b) with a CHECK that user_a < user_b to store each pair once.
05
Always write cardinality both ways in words: "a customer has zero or more orders; an order belongs to exactly one customer". It forces the optionality decisions that become NOT NULL constraints.

Code & diagrams

relationships.sqlsql

-- one-to-many, mandatory on the many side
CREATE TABLE orders (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customer(id),
  created_at  timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX ON orders (customer_id);   -- Postgres does NOT index FKs automatically

-- one-to-one extension
CREATE TABLE customer_kyc (
  customer_id bigint PRIMARY KEY REFERENCES customer(id),
  pan_number  text NOT NULL,
  verified_at timestamptz
);

-- many-to-many with attributes (associative entity)
CREATE TABLE enrolment (
  student_id  bigint NOT NULL REFERENCES student(id),
  course_id   bigint NOT NULL REFERENCES course(id),
  enrolled_at timestamptz NOT NULL DEFAULT now(),
  grade       text,
  PRIMARY KEY (student_id, course_id)
);
CREATE INDEX ON enrolment (course_id);   -- for "students in a course"

-- self-referencing hierarchy and a symmetric graph
CREATE TABLE employee (id bigint PRIMARY KEY, name text NOT NULL,
                       manager_id bigint REFERENCES employee(id));
CREATE TABLE friendship (
  user_a bigint NOT NULL, user_b bigint NOT NULL,
  PRIMARY KEY (user_a, user_b),
  CHECK (user_a < user_b)
);

school-er.mermaiddiagram

Rendering diagram…

Interview problem

The problem

Model a learning platform

Instructors create courses; courses contain ordered sections with ordered lessons; learners enrol in courses, track progress per lesson, and can review a course once. Courses can be co-taught. Give tables, keys and cardinalities.

When it breaks

Unindexed foreign keys

What you see

Deleting a customer scans all of orders to check the FK; joins from parent to children are slow; lock waits appear during deletes.

Fix & prevent

Index every FK column that is joined on or whose parent is deleted or updated. PostgreSQL does not create these automatically (MySQL InnoDB does).

Explain it without notes

When would you model a one-to-one relationship as a separate table instead of extra columns?

Practice

Write a query for "each employee with their manager's name" and explain why it needs a LEFT JOIN.

Trade-offs

↔
Junction tables make many-to-many flexible and constrained; denormalised arrays are faster to read but can't enforce FKs.

Done when you can

I can model any relationship type and state its cardinality and optionality both ways.

Command Palette