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!"Smithers" <A@.B.COM> wrote in message
news:OSoQrlVlGHA.3740@.TK2MSFTNGP02.phx.gbl...
> 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|||Google up "Fifth Normal Form (5NF)"|||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
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:OUsBopVlGHA.2128@.TK2MSFTNGP04.phx.gbl...
> "Smithers" <A@.B.COM> wrote in message
> news:OSoQrlVlGHA.3740@.TK2MSFTNGP02.phx.gbl...
> 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|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:Ol7roDWlGHA.408@.TK2MSFTNGP03.phx.gbl...
> 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|||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
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:elGU93WlGHA.2392@.TK2MSFTNGP04.phx.gbl...
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:Ol7roDWlGHA.408@.TK2MSFTNGP03.phx.gbl...
>
> 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 Rowland wrote:
> I disagree.
> More has to be done to develop a relationship between Teachers and Student
s.
> --
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
--|||I don't know if I am good enough to interefere in the clash of the titans.
But Can't we have something like this?
Take it simply.. A student is associated to a particular class by a
particular teacher.
And there is no explicit teacher student relation.
The same example you had quoted.
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
If Student A's Class B is taught by Teacher ZZ
The the student class table should look like this
StudentClass:
StudentClass_ID = 1, Student=A, Class_ID=2
If a student is attending a session, it needs a class and a teacher.
and if such a class is happening then we should be having an entry in the
class table. Am I missing something?
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Hi David (and all in the discussion),
To tell the truth, I haven't really bothered about 5NF till now.Assumed
anything above 3rd was not practical :)
But the post yesterday and the replies intrigues me. I had checked up on a
few websites and the examples that is given there doesn't convince me. Can
you direct me to a website where the explaination is given. And If I am not
able to understand, then I can discuss with reference to that.
Thanks a lot.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"David Portas" wrote:

> Arnie Rowland wrote:
> 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
> --
>