Table Relationships

A relational database earns its name from exactly this topic: relationships between tables. Once data is split across separate tables — customers in one, orders in another, products in a third — the design has to describe how rows in those tables relate to each other. Every relationship between two tables falls into one of three categories: one-to-one, one-to-many, or many-to-many. Recognizing which category applies is one of the most important steps in schema design, because each one is implemented differently.

One-to-one

In a one-to-one relationship, each row in table A relates to at most one row in table B, and vice versa. A real-world example is a person and their passport: one person has at most one passport, and one passport belongs to exactly one person. One-to-one relationships are the least common of the three, and are often used to split a table into a "core" part and an "optional extra" part — for example, keeping frequently-accessed user data separate from rarely-accessed profile details.

One-to-one: people and passports

SQL

CREATE TABLE people (
  person_id INT PRIMARY KEY,
  full_name VARCHAR(100)
);

CREATE TABLE passports (
  passport_id  INT PRIMARY KEY,
  person_id    INT UNIQUE NOT NULL,
  passport_no  VARCHAR(20),
  expires_on   DATE,
  FOREIGN KEY (person_id) REFERENCES people(person_id)
);

The important detail is the UNIQUE constraint on person_id in the passports table. Without it, this would just be an ordinary one-to-many relationship, since nothing would stop the same person_id from appearing on multiple passport rows. UNIQUE is what enforces "at most one" on the foreign key side.

One-to-many

In a one-to-many relationship, one row in table A can relate to many rows in table B, but each row in table B relates back to only one row in table A. A customer and their orders is the textbook example: a customer can place many orders, but each order belongs to exactly one customer. This is by far the most common relationship type in everyday schema design.

One-to-many: customers and orders

SQL

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        VARCHAR(50)
);

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT NOT NULL,
  order_date  DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Notice that the foreign key lives on the "many" side (orders), pointing back to the "one" side (customers). This is the general rule for one-to-many: the table representing the "many" side holds the foreign key.

Many-to-many

In a many-to-many relationship, many rows in table A can relate to many rows in table B, and vice versa. Students and courses are the classic example: a student can enroll in many courses, and a course can have many students enrolled.

This is the one case a single foreign key cannot express. A foreign key column can only ever point to one row on the other side, so there is no way to put a "courses" foreign key directly on the students table (a student takes several courses, not one) or a "students" foreign key directly on the courses table (a course has several students, not one). The solution is a third table — usually called a junction table, bridge table, or associative table — that sits between the two and stores one row per pairing.

Many-to-many: students and courses via a junction table

SQL

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  name       VARCHAR(50)
);

CREATE TABLE courses (
  course_id INT PRIMARY KEY,
  title     VARCHAR(100)
);

-- Junction table: one row per (student, course) pairing
CREATE TABLE enrollments (
  student_id  INT NOT NULL,
  course_id   INT NOT NULL,
  enrolled_on DATE,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

INSERT INTO enrollments (student_id, course_id, enrolled_on) VALUES
  (1, 10, '2024-09-01'),  -- Alice takes Databases
  (1, 11, '2024-09-01'),  -- Alice also takes Networks
  (2, 10, '2024-09-02');  -- Bob takes Databases too

Each row in enrollments represents one student taking one course. Because enrollments holds a foreign key to students and a separate foreign key to courses, a single student row can be paired with many enrollments rows, and a single course row can also be paired with many enrollments rows — giving the many-to-many behavior without ever needing a column that holds more than one value.

Choosing the right implementation

Relationship	Real-world example	Implementation
One-to-one	A person and their passport	Foreign key on either side, with a UNIQUE constraint on that foreign key column.
One-to-many	A customer and their orders	A plain foreign key on the "many" side, referencing the primary key of the "one" side.
Many-to-many	Students and courses	A junction table with two foreign keys, one to each related table, usually combined into a composite primary key.

Spotting the relationship type before writing SQL

Ask two questions about any pair of entities: "Can one A have several Bs?" and "Can one B have several As?" Both "no" means one-to-one. One "yes" and one "no" means one-to-many. Both "yes" means many-to-many and you will need a junction table. Working this out on paper first — the subject of the next page on ER diagrams — avoids a lot of rework later.

One-to-one: at most one match on each side, enforced with a UNIQUE foreign key.
One-to-many: the foreign key lives on the "many" side and points back to the "one" side.
Many-to-many: cannot be expressed with a single foreign key — requires a junction table with two foreign keys.
A junction table's primary key is often the composite of both foreign keys, since a given pairing should only occur once.