A relationship in Database Management System (DBMS) is defined as the way in which two or more data tables or data entities are linked. The data is typically represented in a relational database in the form of tables; where the columns of table represent the type of information all entities possess and row represents a complete record of information for a single entity of table.

There is always a column in a data table that is assigned the primary key for that table and its purpose is to uniquely identify each row of a column. In the below example, the ID column serves the purpose of primary key.

Customer Table

There are 3 basic types of relationships that can exist between the data tables. They are

• 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 exists when one row of a table is related to only one row of another data table and vice versa. This type of relationship between tables is only used rarely.

In the below table you can visualise the one-to-one relationship. Each row in the customer table can only be linked to only one row of account no: table as each customer can have only one and unique account linked to them.

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

A one to many relationship exists when one row of table 1 can be related to many rows of table 2 and many rows of table 2 can be related to one row of table 1. However, a single record in table 2 can be related to only one record in table 1. This is the most common relationship you will find between data and is also the most widely used.

In the below table, you can visualise the one-to-many relationship. Each row in the customer table can be linked to many rows in the orders table simply because a customer can create multiple orders. However, a single order in orders table can only be related to only one customer in customers table

Many-to-Many relationship

A Many-to-Many relationship exists when many rows of table 1 can be related to many rows of table 2 and many rows of table 2 can be related to many rows of table 1. In the below example Students and Courses table share many to many relationships. A single student can enrol in multiple courses and any specific course can have many students enrolled.

To manage many to many relationships between two tables, a third table is usually created which contains as columns the reference (usually primary key) for the mentioned two tables. This third table is also referred to as the Join table. In the below example we have created a Join table called Enrolled courses in between Students and Courses. Here we are storing which student has enrolled for which course.

Now let’s say we want to find out how many courses a specific student has enrolled in, then we can simply run a query to fetch all rows from the Enrolled courses table where Student Reference ID = ID of the student we are interested in.

‍

Conclusion

In conclusion, understanding data relations is crucial for building effective and efficient databases. By defining and enforcing relationships between data tables, we can ensure data integrity, eliminate redundant data, and facilitate easier data manipulation and analysis. Whether you're designing a new database or working with an existing one, paying attention to data relations will help you create a more robust and reliable data management system.