SQL Server Performance Forum – Threads Archive
many-to-many
Hello, Please provide some light on the specific issue behind many-to-many relationships.Let’s take the classic student-course many-to-many relationships. To answer the question, "which students are enrolled to this course?" or "which courses have been enrolled by this student?", one solution would be to put the CoursePK in the student table and/or the StudentPK in the Course table right? The issue I can see here is that I would be storing unnecessary redundant data. Like I need to repeat student rows (and all its properties) for each course in the student table. OR, I need to repeat course rows (And all its properties) for each student in the course table. Another issue is that if I have an attribute like a course grade (which is something that will have a value if a student is enrolled to a course), I cannot put this attribute in just one table (either the student or the course) since its really an attribute of the relationship. So the question is, are these the reasons (duplicate data, functional dependency) why the creation of an association or intersection table is recommended? If I dont use an associative table and use the other approach even if Id have duplicate data, would I have problems in answering the previous questions("which students are enrolled to this course?" or "which courses have been enrolled by this student?")? Would I have problems if I try to join the 2 tables? Thanks! rache
No, you have a middle table tblStudentCourse that has (StudentId, CourseId) as PK. You add all the enrollment details here, and leave the student and course details in the respective tables. To find all students for a given course:
SELECT s.StudentName
FROM tblStudentCourse sc
INNER JOIN tblStudent s ON s.StudentId = sc.StudentId
INNER JOIN tblCourse c ON sc.CourseId = c.CourseId
WHERE c.CourseName = ‘MyCourse’ To find all courses for a given student:
SELECT c.CourseName
FROM tblStudentCourse sc
INNER JOIN tblStudent s ON s.StudentId = sc.StudentId
INNER JOIN tblCourse c ON sc.CourseId = c.CourseId
WHERE s.StudentName = ‘MyStudent’
I like this page for it’s simplicity.
It describes some of the problems with different models etc. http://www.datamodel.org/reference.php For the question about ‘many to many’ – see the ‘cardinality explained’ link.
It gives a brief overview of pros and cons in different situations. /Kenneth
]]>