One-to-many relationship design in SQL

Introduction

A one-to-many relationship helps connect two sets of data where one item links to many others. It keeps data organized and easy to find.

When you have customers and each customer can place many orders.

When a blog has many posts, but each post belongs to one author.

When a school has many students, but each student belongs to one class.

When a company has many employees, but each employee works in one department.

Syntax

SQL

CREATE TABLE ParentTable (
  id INT PRIMARY KEY,
  other_columns ...
);

CREATE TABLE ChildTable (
  id INT PRIMARY KEY,
  parent_id INT,
  other_columns ...,
  FOREIGN KEY (parent_id) REFERENCES ParentTable(id)
);

The parent_id in the child table links to the id in the parent table.

This setup ensures each child row belongs to one parent row.

Examples

Each book is linked to one author, but one author can have many books.

SQL

CREATE TABLE Authors (
  author_id INT PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE Books (
  book_id INT PRIMARY KEY,
  title VARCHAR(100),
  author_id INT,
  FOREIGN KEY (author_id) REFERENCES Authors(author_id)
);

Each employee belongs to one department, but a department can have many employees.

SQL

CREATE TABLE Departments (
  dept_id INT PRIMARY KEY,
  dept_name VARCHAR(50)
);

CREATE TABLE Employees (
  emp_id INT PRIMARY KEY,
  emp_name VARCHAR(100),
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
);

Sample Program

This example creates customers and their orders. Then it shows which orders belong to which customer.

SQL

CREATE TABLE Customers (
  customer_id INT PRIMARY KEY,
  customer_name VARCHAR(50)
);

CREATE TABLE Orders (
  order_id INT PRIMARY KEY,
  order_date DATE,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

INSERT INTO Customers VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO Orders VALUES (101, '2024-06-01', 1), (102, '2024-06-02', 1), (103, '2024-06-03', 2);

SELECT c.customer_name, o.order_id, o.order_date
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_id;

Important Notes

Always define the foreign key to keep data linked correctly.

Deleting a parent row may affect child rows if not handled carefully.

Use indexes on foreign keys for faster queries.

Summary

One-to-many links one record to many records in another table.

Use a foreign key in the 'many' table to connect to the 'one' table.

This design helps keep data organized and easy to query.