One to Many relationship examples in Database

There are 3 main types of database relationships in relational database design.

  1. One-to-Many (or Many-to-One) relationship
  2. One-to-One relationship
  3. Many-to-Many relationship

One-to-Many Relationships

One-to-Many Relationship exists when a single record in the 1st table is having a relationship with many records in the 2nd table.

one to many relationship in php mysqli
one to many relationship in php mysqli

One-to-Many Relationship Examples

Let’s begin with an ERD design in which one student can have only one scholarship.

Entities

  1. Employee
  2. Salary_slips

Attributes of Employee

  1. Employee_id
  2. Employee_name

Attributes of Salary_slips

  1. Salary_issued
  2. Year
  3. Month
  4. Total_Salary

Relationship

One “Employee” can have many “Salary_slips”.

one to many relationship examples in database systems
Figure: Entity Relationship Diagram of 1 to Many Relationship

How to convert ERD into database tables and showing a 1 to M relationship?

Now, let’s convert this ERD Design to database tables.

As, we have two entities and one relationship, so we need to make three tables, two tables for two entities and one table for this relationship.

  1. The first table is for the “Employee” entity.
  2. The second table is for a relationship that an Employee “can_get” Salary_slips.
  3. The third table is for the “Salary_slips” entity.
one to many relationship in DBMS
Figure: Database tables representation of 1 to M Relationship

Non-Key Attributes: are the attributes without any key.

Primary-Key Attributes: are the attributes with the primary key and used for the unique identification of any row. Any value in the primary key attribute can’t be repeated again and again.

Foreign- Key Attributes are the attributes that set a foreign key. A foreign key is the primary key of one table that is linked with the field of another table.

Detailed information about keys is available in the keys tutorial.

In this example, we have set the primary key with “Salary_slip_id” in the 2nd table because the values of this attribute are unique and values are not repeating again and again.

Next Articles

One-to-Many relationship example in SQL | when to use a one-to-Many relationship | one-to-Many relationship diagram | one to Many relationships in MySQL | one to Many relationships in the SQL server | one to Many relationships in XAMPP PHP Myadmin | one-to-Many relationship ER diagram.

Foreign Key behaving differently on  Cascade strict and SET Null