Re: Junction table

"annalissa" <aarklon_at_gmail.com> wrote in message news:h2pd8g$kld$2_at_aioe.org...
> hi all,
>
> i saw an explanation of junction table here:-
> http://megocode3.wordpress.com/2008/01/04/understanding-a-sql-junction-table/
> how correct is this explanation ?

It is correct; however, it doesn't sufficiently address the claim that junction tables prevent the addition of duplicate entries, nor the theoretical basis for such a design.

If you try to stuff everything into one table, call it BadStudentClassrooms

CREATE TABLE BadStudentClassrooms
(

    StudentName nchar(50) NOT NULL,
    RoomNumber int NOT NULL,
    PRIMARY KEY (StudentName, RoomNumber) )

Then you can't insert a student without inserting a classroom and you can't insert a classroom without inserting a student. These are known as insert anomalies. The issue is that it is possible for a student to have been assigned one or more classrooms but it is not necessary and that it is possible for a classroom to have been assigned one or more students but it is not necessary. If students could not exist outside of classrooms and classrooms could not exist without students, then BadStudentClassrooms would not be bad, even though there is a many to many relationship between students and classrooms. It is the zeros in the multiplicities on the ends of the relationship, 0..m:0..n, that indicates that a junction table is required. In fact, for a 1..m:1..n relationship, a junction table is problematic because as far as I know, SQL implementations don't support the declaration of generalized inclusion dependencies (in SQL, what is referenced must be the columns in a primary key or unique constraint), so they must be enforced through the use of triggers.

>
> but SELECT StudentName, RoomNumber
> FROM StudentClassroom in this query
>
> SELECT StudentName, RoomNumber
> FROM StudentClassroom
> JOIN Students ON Students.StudentID = StudentClassroom.StudentID
> JOIN Classrooms ON Classrooms.ClassroomID = StudentClassroom.ClassroomID
>
> doesn't seem to be correct is n't it?

It is. Neither StudentName nor RoomNumber is ambiguous in the query, so they don't need to be qualified in the query, unlike StudentID and ClassroomID. And neither StudentID nor ClassroomID need to be included in the select list. Received on Sun Jul 05 2009 - 15:05:48 CEST