Types of Relationship in DBMS

Introduction

As said above, any association between two entity types is called a relationship. For example, a mother feeds her baby. Here "feeds" is a relationship, and this is the relationship between a child and the mother.

Relationships allow the datasets to share and store data in separate tables. Relationships are of three types in DBMS and the next section explains those different types of relationships in DBMS with examples.

That is how a relationship can be defined between the tables. Three types of relationships can exist between two entities, which are given below and also discussed in this article with examples.

• One-to-One relationship
• One-to-Many relationship or Many-to-One relationship
• Many-to-Many relationship

One-to-One Relationship

A One-to-one relationship means a single record in Table A is related to the single record in Table B and vice-versa.

For example, If there are two entities, 'Person'(Name, age, address, contact no.) and 'Aadhar card'(Name, Aadhar no.). So each person can have only one aadhar card, and the single aadhar card belongs to only one person.

You can visualize the one-to-one relationship in DBMS like this:

This type of relationship is used for security purposes. In the above example, we can store the aadhar card number in the same entity 'Person', but we created another table for the 'Aadhar card' because the aadhar card number may be sensitive data and should be hidden from others. Let's take another example with the databases.

Example:

Consider a table of Employee as shown below:

Table A:

Now, you can place the employee address in a separate table as shown below:

Employee:

Employee Address:

Now, the relationship between the Employees table and Employee Address can be created. In the above example, one emp_address_id belongs to only one emp_address, that is, the relationship is a one-to-one relationship. This type of relationship is not very common as the employee's address can also be included in the Employee table as shown in table A and this would work too.

One-to-Many Relationship

Such a relationship exists when each record of table A can be related to one or more records of another table i.e., table B. However, a single record in table B will have a link to a single record in table A. This is the most common relationship you will find that is widely used. A one-to-many relationship in DBMS can also be named a many-to-one relationship, depending on the way we view it.

The one-to-many relationship in DBMS exists between the pair of tables if a single record in one table is related to one or more records of the other table. For example, if there are two entities, 'Customer' and 'Account', then each customer can have more than one account, and also, each account is owned by one customer only.

You can visualize the one-to-many relationship in DBMS like this:

Example:

Consider a table of Customers and Orders as shown below:

Customers:

Orders:

The relationship between the above two tables can be visualized like this:

In this example, there is a one-to-many relationship if looked at from the perspective of the Customers. As shown above, the customer_id - 222 is related to the three different order_id. In a similar way, there is a many-to-one relationship between the tables if looked at from the perspective of the Orders table.

Many-to-Many Relationship

A many-to-many relationship exists between the tables if a single record of the first table is related to one or more records of the second table and a single record in the second table is related to one or more records of the first table. Consider the tables A and B. In a many-to-many relationships, each record in table A can be linked to one or more records in table B and vice-versa. It is also represented as N:N relationship.

For example, consider the two tables i.e., a student table and a courses table. A particular student may enroll himself in one or more than one course, while a course also may have one or more students. Hence, this is a great example of many-to-many relationships.

You can visualize the many-to-many relationship in DBMS like this:

A many-to-many relationship from the perspective of table A.

A many-to-many relationship from the perspective of table B.

Example:

Consider the tables of Orders, Items, and Items Orders as shown below:

Orders:

Items:

Items Orders:

The relationship between the above two tables can be visualized like this:

As shown in the above example, the table Items Orders has only one purpose, and that is to create many-to-many relationships between the items.

Read More:

• Degree of Relationship in DBMS.