One-To-Many Relationship

Back to: Structured Query Language (SQL)

---

One-To-Many Relationship signifies connection between two entities where one entity is associated with multiple instances of the other entity but each instance of second entity is associated with only one instance of first entity. Depending on how we look at it, a one-to-many relationship is also called many-to-one relationship. It is implemented using the concept of foreign keys.

Here are some examples of one-to-many relationship:

• A car maker makes many different models, but only one car maker builds a particular model.
• One customer may make several purchases, but each purchase is made by a single customer.
• A company can have many phone numbers, but a phone number belongs to one company.

A one-to-many relationship is not a property of the data, but rather of the relationship itself. It is also called a foreign key constraint, which is important to keep data from being duplicated and have relationships within the database stay reliable as more information is needed. For example, a customer has many orders, but each order can have only one customer.

In a one-to-many relationship in a database management system (DBMS), one record in the first table can be associated with one or more records in the second table. This is a fundamental concept in relational database design. In SQL, a one-to-many relationship is established using foreign keys.

As an example, we will consider a case where each customer can place multiple orders, but each order is associated with only one customer. This is the example of one-to-many relationship. Here’s how one-to-many relationship is implemented:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(50)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- Inserting sample data for Customers
INSERT INTO Customers (CustomerID, Name) VALUES
(1, 'Bob Down'),
(2, 'Ben Dover'),
(3, 'Anita Bath');

-- Inserting sample data for Orders
INSERT INTO Orders (OrderID, CustomerID, OrderDate) VALUES
(101, 1, '2025-03-09'),
(102, 1, '2025-03-08'),
(103, 2, '2025-03-07'),
(104, 3, '2025-03-06');

SELECT * FROM Customers;

SELECT * FROM Orders;

This makes two tables and fills them with some data:

Visualizing the one-to-many relationship using left join as:

SELECT * FROM Customers 
LEFT JOIN Orders 
ON Customers.CustomerID = Orders.CustomerID;

In this example, ‘Customers‘ table is parent table with ‘CustomerID‘ as primary key and ‘Orders‘ table is the child table where ‘CustomerID‘ is a foreign key referencing the ‘CustomerID‘ column in the ‘Customers‘ table.

Example 2: Department and Employees

Another example is the relation between department and employees in an organization. Each department can have multiple employees, but each employee can belong only to one department.

CREATE TABLE Departments (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(50)
);

CREATE TABLE Employees (
    EmpID INT PRIMARY KEY,
    EmpName VARCHAR(50),
    DeptID INT,
    FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);

-- Inserting sample data for Departments
INSERT INTO Departments (DeptID, DeptName) VALUES
(101, 'Engineering'),
(102, 'Sales'),
(103, 'Marketing');

-- Inserting sample data for Employees
INSERT INTO Employees (EmpID, EmpName, DeptID) VALUES
(201, 'Eileen Dover', 101),
(202, 'Dee Niall', 101),
(203, 'Justin Time', 102),
(204, 'Patty O’Furniture', 102),
(205, 'Ella Vator', 103);

SELECT * FROM Employees;

This makes two tables and fills them with some data:

Visualizing the one-to-many relationship with help left join as:

SELECT * FROM Departments 
LEFT JOIN Employees
ON Departments.DeptID = Employees.DeptId;

Here, the ‘Departments‘ table acts as the parent table, and the ‘Employees‘ table is the child table. The ‘DeptID‘ column in the ‘Employees‘ table serves as a foreign key referencing the ‘DeptID‘ column in the ‘Departments‘ table.

The one-to-many relationship helps in organising the data in databases effectively. For example, how each customer can make several orders, but each order belongs to only one customer. By setting up these connections correctly using the concept of foreign keys, we ensure that our data stays organised. So, whether it’s tracking orders for customers or assigning employees to departments, understanding and using one-to-many relationships helps keep our data neat and usable.

---