There are 3 main types of database relationships in relational database design.
- One-to-Many (or Many-to-One) relationship
- One-to-One relationship
- 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 Examples
Let’s begin with an ERD design in which one student can have only one scholarship.
Entities
- Employee
- Salary_slips
Attributes of Employee
- Employee_id
- Employee_name
Attributes of Salary_slips
- Salary_issued
- Year
- Month
- Total_Salary
Relationship
One “Employee” can have many “Salary_slips”.
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.
- The first table is for the “Employee” entity.
- The second table is for a relationship that an Employee “can_get” Salary_slips.
- The third table is for the “Salary_slips” entity.
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-One relationship
- Many-to-Many relationship
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.