Table relationships: one-to-one, one-to-many and many-to-many

A relational database is called that because its tables are related to one another. Being able to recognise what kind of relationship exists between two entities, and to translate it into the right structure, is perhaps the most important skill in schema design. A mistake at this stage brings problems that last for the whole life of the application.

How to recognise the type of relationship

The simplest method is to ask two questions, one for each direction. For customers and orders:

• can a customer have several orders? Yes.
• can an order belong to several customers? No.

One «many» direction and one «one»: it is a one-to-many relationship. For students and courses:

• can a student take several courses? Yes.
• can a course have several students? Yes.

Two «many» directions: it is a many-to-many relationship.

One-to-many

This is the most common relationship. It is represented with a foreign key in the table on the «many» side, pointing to the primary key of the «one» side:

CREATE TABLE orders (
  id          BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id),
  order_date  DATE NOT NULL
);

The foreign key guarantees that every order points to an existing customer. The NOT NULL constraint adds another rule: an order cannot exist without a customer. If the relationship is optional instead, for example a support ticket that may not yet be assigned to a technician, the column stays nullable.

The typical mistake is to do the opposite: putting columns such as order1, order2, order3 in the customers table. You immediately have an arbitrary limit and the queries become complicated.

Many-to-many

A many-to-many relationship cannot be represented with a single foreign key. It needs a third table, called a junction, association or link table, containing the keys of the two entities:

CREATE TABLE enrolments (
  student_id BIGINT NOT NULL REFERENCES students(id),
  course_id  BIGINT NOT NULL REFERENCES courses(id),
  PRIMARY KEY (student_id, course_id)
);

The composite primary key prevents the same student from being enrolled twice on the same course. To find a student's courses, you join the three tables.

The junction table with attributes

Often the relationship itself has properties. An enrolment has a date, a final grade, a status. This information belongs neither to the student nor to the course, but to their combination, and it goes in the junction table:

CREATE TABLE enrolments (
  student_id    BIGINT NOT NULL REFERENCES students(id),
  course_id     BIGINT NOT NULL REFERENCES courses(id),
  enrolled_on   DATE NOT NULL,
  grade         SMALLINT CHECK (grade BETWEEN 18 AND 30),
  PRIMARY KEY (student_id, course_id)
);

At this point the junction table has become a fully fledged entity. The same happens with order lines, which link orders and products and hold quantity and price.

A question worth asking: can the same student enrol on the same course twice, for example in different years? If so, the composite key is no longer enough, and you need to add the year to the key or use a surrogate key with an appropriate uniqueness constraint.

One-to-one

In a one-to-one relationship, each row in one table corresponds to at most one row in the other. It is represented with a foreign key also declared UNIQUE, or by using the same primary key in both tables.

If two entities really are one-to-one, you might ask why not merge them into a single table. There are good reasons to keep them separate:

• optional data: only some users have a business profile with many specific fields;
• sensitive data: confidential information in a table with tighter permissions;
• heavy data: documents or long texts that would slow down reads of the main table.

Self-referencing relationships

A table can have a foreign key pointing to itself. It is the natural way to represent hierarchies:

CREATE TABLE employees (
  id         BIGINT PRIMARY KEY,
  name       TEXT NOT NULL,
  manager_id BIGINT REFERENCES employees(id)
);

The director has an empty manager_id; everyone else points to their own manager. The same pattern works for categories and subcategories or for comments and replies. To walk the entire hierarchy you use recursive queries, available in most databases with the WITH RECURSIVE syntax.

What happens on delete

When a customer is deleted, what should happen to their orders? The foreign key lets you declare it:

CASCADE is convenient but dangerous: a single delete can chain across many tables. For data with historical value, such as customers with invoices, it is better to prevent deletion and use a column marking the record as deactivated instead.

In short

• The type of relationship is recognised by asking «how many?» in both directions.
• One-to-many: a foreign key in the table on the «many» side.
• Many-to-many: a junction table, which can have its own attributes and become an entity.
• One-to-one: a unique foreign key, useful for optional, sensitive or heavy data.
• Delete actions must be chosen carefully; CASCADE only for dependent data.