Skip to content
BENIBIX Learn

One-to-Many Relationship

Development🗃️ DatabasesDatabase Basics

Also known as One-to-Many, 1:N Relationship, Table Relationship, Parent-Child Table

A link between two tables. One row on one side goes with many rows on the other.

Think of it like

It is like a mum and her kids. One mum can have many kids, but each kid has one mum.

Example

Lucy's blog has authors and posts. Each post stores an author_id, so one author can have many posts.

Why it matters

It is the most common link between tables. Customers and orders, posts and comments, and teams and players all work this way.

Definition

A one-to-many relationship links two tables. One row in the first goes with many rows in the second. One customer can have many orders, but each order has one customer. You build it with a foreign key. The table on the many side holds a column that points back to the one side, like customer_id on each order.

Example code

sql
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  total NUMERIC
);
Each order points back to one customer. One customer can have many orders.
One customer at the bar end, many orders at the crow's foot end.

Common mistake

Putting the link on the wrong side, like a list of order ids in the customer row. The foreign key goes on the many side.

Key takeaways

  1. 1
    One side, many side. One customer, many orders.
  2. 2
    Key on the many side. Each order holds a customer_id.
  3. 3
    Join to read it. A join brings both tables together.