Type Of Relationships In SQL
In this lesson, you will learn how to distribute data into several tables correctly so you can link them together again when you need to retrieve them.
Introduction To Tables Relationships
When building a database, you must have accurate knowledge about the information that needs to be stored in the project to know what type of logical relationship between tables should you create.
So relationships are used to define how different tables are connected or related to each other. These relationships are crucial for organizing and structuring data in a way that avoids redundancy and ensures data integrity.
There are three main types of relationships in SQL databases:
- One-to-one
- One-to-many
- Many-to-many
The program that you are using to build the database can help you a lot to view how the tables are related. MySQL workbench allows you to view how the tables are related.
One To One Relationship
A one-to-one relationship means that each value in the table can be used only once by the other table.
For example, you are allowed to have one passport from your country. And if you want to get a new one, you have to cancel the old one first.
How to draw a one-to-one relationship
The one-to-one relationship can represented by a line between the two tables with the symbol 1 : 1 on it.
Or by putting a 1 at each end of the line.
Or by putting a vertical line at each end of the line.
If the relationship between the tables is drawn precisely where the line is connect the common columns between them, we can put any of the previous symbols to indicate that the relationship between them is one-to-one.
How data are stored in a one-to-one relationship
The following image shows how data are stored in tables if the relationship type is one-to-one.
Note that in the passports table, a person_id cannot be present more than once because, by logic, a person is not allowed to have more than one passport.
One To Many Relationship
A one-to-many relationship means that each value in the table can be used many times by the other table.
For example, you are allowed to publish as many posts as you want on social media.
How to draw a one-to-many relationship
The one-to-many relationship can be represented by a line between the two tables with the symbol 1 : M on it.
Or by putting a 1 at one end and an M at the other end.
Or by putting a 1 at one end and a % at the other end.
Or by putting a vertical line at one end and three vertical lines at the other end.
If the relationship between the tables is drawn precisely where the line is connect the common columns between them, we can put any of the previous symbols to indicate that the relationship between them is one-to-many.
How data are stored in a one-to-many relationship
The following image shows how data are stored in tables if the relationship type is one-to-many.
Note that in the posts table, a user_id can exist more than once because by logic a person can write more than one post or article.
Many To Many Relationship
In a many-to-many relationship, each record in the table can be related to one or more records in the other table, and vice versa.
This type of relationship is implemented using a junction table (also called an associative or link table) that connects the two tables.
For example, suppose we have a table for students and a table for courses, where each student can enroll in multiple courses, and each course can have multiple students. To link these tables together we need an intermediate table between them.
So a many-to-many relationship is made by using 2 one-to-many relationships.
How to draw a many-to-many relationship
The many-to-many relationship can be represented by lines that connect the main tables and the junction table with the symbol 1 : M on each one.
Or by putting a 1 at one end and an M at the other end of each line.
Or by putting a 1 at one end and a % at the other end of each line.
Or by putting a vertical line at one end and three vertical lines at the other end of each line.
If the relationship between the tables is drawn precisely where the line is connect the common columns between them, we can put any of the previous symbols to indicate that the relationship between them is many-to-many.
How data are stored in a many-to-many relationship
The following image shows how data are stored in tables if the relationship type is many-to-many.
Notice how the student can register in several subjects at the same time, and in one subject several students can be registered at the same time as well.
To read the information from the students_courses table understandably, you can replace the student_id with the student name, and replace the course_id with the course name. Then, the information can be read either for each student or each subject.
Reading students_courses data for each student:
- Emily Johnson is registered in the Algorithms and Network 1 courses.
- William Davis is registered in the Algorithms and Web Development courses.
Reading students_courses data for each course:
- In the Algorithms course, the students Emily Johson and Adam Smith are registered.
- In the Network 1 course, the student Emily Johson is registered.
- In the Web Development course, the student William Davis is registered.
