Many-to-Many Variation  
Author Message
Big0ne





PostPosted: Wed Jun 21 12:44:52 CDT 2006 Top

SQL Server Developer >> Many-to-Many Variation

In establishing a many-to-many relationship between two tables, a 3rd
"junction" table is created that contains at least two foreign keys.

No problem.

But - what is a recommended way to proceed to create a many-to-many
relationship between (1) one table, and (2) two other tables.

For example, say you are designing a database for a school and you are
establishing a many-to-many relationship between [Classes] and [Students]
and [Teachers].

There would obviously be a Classes table. A "junction table" could then have
foreign keys to Classes and Students. That would get us a many-to-many
relationship between Classes and Students. But we also need to show that
relationship between Classes and Teachers.

What do we do with the "junction table"? Do we add a 3rd foreign key to the
existing table? Do we create another junction table altogether?

I'd appreciate some guidance on this.

Thanks!

SQL Server125  
 
 
David





PostPosted: Wed Jun 21 12:44:52 CDT 2006 Top

SQL Server Developer >> Many-to-Many Variation


> In establishing a many-to-many relationship between two tables, a 3rd
> "junction" table is created that contains at least two foreign keys.
>
> No problem.
>
> But - what is a recommended way to proceed to create a many-to-many
> relationship between (1) one table, and (2) two other tables.
>
> For example, say you are designing a database for a school and you are
> establishing a many-to-many relationship between [Classes] and [Students]
> and [Teachers].
>
> There would obviously be a Classes table. A "junction table" could then
> have foreign keys to Classes and Students. That would get us a
> many-to-many relationship between Classes and Students. But we also need
> to show that relationship between Classes and Teachers.
>
> What do we do with the "junction table"? Do we add a 3rd foreign key to
> the existing table? Do we create another junction table altogether?
>

Assuming that a class has many teachers, you would use seperate junction
table. Each junction table models a different relation. One models the
"student is a member of class" relation, and the other models "class is
taught by teacher" relation.

If each class has a single teacher, then add a foreign key on the Classes
table referencing Teachers.

David

 
 
Alexander





PostPosted: Wed Jun 21 12:50:58 CDT 2006 Top

SQL Server Developer >> Many-to-Many Variation Google up "Fifth Normal Form (5NF)"

 
 
Arnie





PostPosted: Wed Jun 21 13:31:25 CDT 2006 Top

SQL Server Developer >> Many-to-Many Variation And then if you want to model the relationship between Teachers and Students
...

--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam




>


>> In establishing a many-to-many relationship between two tables, a 3rd
>> "junction" table is created that contains at least two foreign keys.
>>
>> No problem.
>>
>> But - what is a recommended way to proceed to create a many-to-many
>> relationship between (1) one table, and (2) two other tables.
>>
>> For example, say you are designing a database for a school and you are
>> establishing a many-to-many relationship between [Classes] and [Students]
>> and [Teachers].
>>
>> There would obviously be a Classes table. A "junction table" could then
>> have foreign keys to Classes and Students. That would get us a
>> many-to-many relationship between Classes and Students. But we also need
>> to show that relationship between Classes and Teachers.
>>
>> What do we do with the "junction table"? Do we add a 3rd foreign key to
>> the existing table? Do we create another junction table altogether?
>>
>
> Assuming that a class has many teachers, you would use seperate junction
> table. Each junction table models a different relation. One models the
> "student is a member of class" relation, and the other models "class is
> taught by teacher" relation.
>
> If each class has a single teacher, then add a foreign key on the Classes
> table referencing Teachers.
>
> David


 
 
David





PostPosted: Wed Jun 21 15:05:03 CDT 2006 Top

SQL Server Developer >> Many-to-Many Variation


> And then if you want to model the relationship between Teachers and
> Students ...
>


That information can be derived from the existing relations:

Select Class.Teacher, StudentClass.Student
from Class
join StudentClass
on Class.ID = StudentClass.ID

David



 
 
Arnie





PostPosted: Wed Jun 21 15:49:00 CDT 2006 Top

SQL Server Developer >> Many-to-Many Variation I disagree.

But my disagreement is positioned on having had to work out this kind of
issue before. (I may be overlooking some simple solution you have worked
out. )

Student A has a Class B
StudentClass:
StudentClass_ID = 1, Student=A, Class=B

Teacher ZZ teaches Class B, Teacher YY also teaches Class B.
Class:
Class_ID=1,Class=B, Teacher=YY
Class_ID=2,Class=B, Teacher=ZZ

Student A's Class B is taught by Teacher ZZ

Your query will tell me that Student A has a relationship with Teacher YY
which is NOT true.

More has to be done to develop a relationship between Teachers and Students.

--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam




>


>> And then if you want to model the relationship between Teachers and
>> Students ...
>>
>
>
> That information can be derived from the existing relations:
>
> Select Class.Teacher, StudentClass.Student
> from Class
> join StudentClass
> on Class.ID = StudentClass.ID
>
> David
>
>
>


 
 
David





PostPosted: Wed Jun 21 16:12:15 CDT 2006 Top

SQL Server Developer >> Many-to-Many Variation
> I disagree.
>
> More has to be done to develop a relationship between Teachers and Students.
>
> --

Correct. This problem is solved by 5th Normal Form.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--