Managing Many-to-Many relationship

DatabaseMCARDBMS

Many to Many relationship implies multiple relationship between any two entities. An example of this is: A class has multiple students but a student also attends multiple classes. So this is a many to many relationship between the entities STUDENT and CLASS.

It is complicated to manage a many to many relationship. We cannot mention all the classes a student attends or all the students in a class in one table. This would become extremely complicated and difficult to understand. 

So, we have used a joining table. This is demonstrated as follows −

The Student table stores the details of individual students such as their Student ID, name, age etc.

<Student>

Student_ID
Student_Name
Student_Age
1
Amit
27
2
Tom23
3
Jack32


The Class table stores the details of all the different classes such as the ClassID, Name etc.

<Class>

Class_ID
Class_Name
10
English
20
Hindi
30
Mathematics


The  <StudentClass> table is the link between the <Student> and <Class> tables. It stores details about which student attends which classes.

<StudentClass>

Student_ID
Class_ID
1
10
1
20
2
10
2
30
3
10


Using this table we can showcase the many to many relationship between <Student> and <Class>. We can easily update the Student and Class tables without interfering with the relationship between them and also update the <StudentClass> as required.

raja
Published on 24-Jul-2018 17:36:01
Advertisements