How do I correctly design a many-to-many relationship in SQL

Question

Hi sir How to implement a many-to-many relationship in SQL Server via the designer? Kindly give me the solution for this problem. [Note: How to design a tables that implement many to many relationship in sql server]

Mesbah Ahmed · Answer

You will need to introduce an associative entity in the middle. The associative entity should have the primary keys of the other two M:M entities as its own concatenated (i.e. composite primary key). In short in all relational database systems we need to decompose a M:M relationship to two 1:M relationship getting three tables (instead of two). For example suppose that a student enrolls in many classes and a class has many students. In this case we need to design three tables: student (sid sname blah blah); course (courseNumber title creditHours blah blah) and most importantly the Enrollment table with (sid CourseNumber semester year etc.).