One-To-Many Relationship Example

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.

Main Concept and Syntax

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.

Main Concept

• One-to-many relationships are characterized by one entity (or record) in a table having multiple related entities (or records) in another table.
• This relationship is established using foreign keys, where the primary key of the "one" table is referenced as a foreign key in the "many" table.
• It allow us for efficient data organization and ensuring data integrity and reducing redundancy.

Syntax

CREATE TABLE ParentTable (
    ParentID INT PRIMARY KEY,
    ParentAttribute DataType
);

CREATE TABLE ChildTable (
    ChildID INT PRIMARY KEY,
    ChildAttribute DataType,
    ParentID INT,
    FOREIGN KEY (ParentID) REFERENCES ParentTable(ParentID)
);

Examples

Example 1: Customers and Orders

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, 'Shivansh Mahajan'),
(2, 'Rakesh Kumar'),
(3, 'Vikram kumar');

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

Select * from Customers;

Select * from Orders;

Customers table

Orders table

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, 'Shivansh Mahajan', 101),
(202, 'Rupesh Kumar', 101),
(203, 'Kunal Kumar', 102),
(204, 'Mamta kumari', 102),
(205, 'Priya Mehta', 103);


Select * from Departments;

Select * from Employees;

Department table

Employees table

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.

Conclusion

Learning about the one-to-many relationship helps in organizing the data in databases effectively. It's like knowing how different things are connected. 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 organized. 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.