Junction Tables

[Khalil Handal]

Hi,
Where can I find an example - tutorial - about a "junction table"?
(or an explanation about how it is used).

 

[Allen Browne]

The junction table is the standard way to break down a many-to-many relation
into a pair of one-to-many relations. It's probably easiest to explain by
viewing examples.

Here's a basic example:
Relationships between Tables
at:
http://allenbrowne.com/casu-06.html
The Grades table is a junction between Students and Subjects.

Here's another example:
Don't use Yes/No fields to store preferences
at:
http://allenbrowne.com/casu-23.html
The StudentSport table is a junction between students and sports.

For further reading, see any of these articles on normalization:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

 

[Khalil Handal]

Thanks!

The junction table is the standard way to break down a many-to-many
relation into a pair of one-to-many relations. It's probably easiest to
explain by viewing examples.

Here's a basic example:
Relationships between Tables
at:
http://allenbrowne.com/casu-06.html
The Grades table is a junction between Students and Subjects.

Here's another example:
Don't use Yes/No fields to store preferences
at:
http://allenbrowne.com/casu-23.html
The StudentSport table is a junction between students and sports.

For further reading, see any of these articles on normalization:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

 

[Ken Sheridan]

Remember that a 'junction' table can also model a relationship type between
more than two tables. You might like to take a look at the final post in the
following recent thread:


http://www.microsoft.com/office/com...cess&mid=90f52baf-481b-4088-8a27-42dce08b1cd8


in which I've summarised this in the context of a 3-way relationship type.

One thing which you might also not be aware of is that a 'junction' table
can also model a simple one-to-many relationship type between two tables.
Normally of course this would be done by having a foreign key column in the
referencing table referencing the primary key column of the referenced table.
However, there may be reasons for avoiding Nulls in the foreign key column
where referencing a row in the referenced table is not appropriate to a row
in the referencing table.

For example in England cities or towns can be in a County (as in my case
where Stafford is in the county of Staffordshire), but large metropolitan
cities, e.g. Manchester or Birmingham are not, being 'unitary authorities'
are no longer in a county (the former Metropolitan Counties having been
abolished some years ago) so a CountyID foreign key column would be Null in
those cases.

Most people would see no serious objection to Nulls in CountyID column, and
would interpret this as meaning that the city is not in a county. Some would
argue, however, that because a Null is not a value but the absence of a value
and consequently semantically ambiguous, Nulls should not be allowed. The
solution to this is to represent the relationship type by a 'junction' table
rather than a foreign key column, i.e. a table with two foreign keys CityID
and CountyID. The only difference between this and the more usual use of a
'junction' table to model a many-to-many relationship type is that in this
case the CityID column is indexed uniquely rather than non-uniquely as there
can of course be only one instance of each CityID value in the table.

BTW on the question of the legitimacy of Nulls my own view is a pragmatic
one. In a case like that above I see no objection to them, but in a case
such as a CreditLimit column in a Customers table I'd rule them out. What
would a Null credit limit mean? No credit? Unlimited credit? There is just
no way of knowing from the data per se, so in this case I'd require a real
value.

Ken Sheridan
Stafford, England

 

[Khalil Handal]

Thanks Ken, it was very helpful

Remember that a 'junction' table can also model a relationship type
between
more than two tables. You might like to take a look at the final post in
the
following recent thread:


http://www.microsoft.com/office/com...cess&mid=90f52baf-481b-4088-8a27-42dce08b1cd8


in which I've summarised this in the context of a 3-way relationship type.

One thing which you might also not be aware of is that a 'junction' table
can also model a simple one-to-many relationship type between two tables.
Normally of course this would be done by having a foreign key column in
the
referencing table referencing the primary key column of the referenced
table.
However, there may be reasons for avoiding Nulls in the foreign key column
where referencing a row in the referenced table is not appropriate to a
row
in the referencing table.

For example in England cities or towns can be in a County (as in my case
where Stafford is in the county of Staffordshire), but large metropolitan
cities, e.g. Manchester or Birmingham are not, being 'unitary authorities'
are no longer in a county (the former Metropolitan Counties having been
abolished some years ago) so a CountyID foreign key column would be Null
in
those cases.

Most people would see no serious objection to Nulls in CountyID column,
and
would interpret this as meaning that the city is not in a county. Some
would
argue, however, that because a Null is not a value but the absence of a
value
and consequently semantically ambiguous, Nulls should not be allowed. The
solution to this is to represent the relationship type by a 'junction'
table
rather than a foreign key column, i.e. a table with two foreign keys
CityID
and CountyID. The only difference between this and the more usual use of
a
'junction' table to model a many-to-many relationship type is that in this
case the CityID column is indexed uniquely rather than non-uniquely as
there
can of course be only one instance of each CityID value in the table.

BTW on the question of the legitimacy of Nulls my own view is a pragmatic
one. In a case like that above I see no objection to them, but in a case
such as a CreditLimit column in a Customers table I'd rule them out. What
would a Null credit limit mean? No credit? Unlimited credit? There is
just
no way of knowing from the data per se, so in this case I'd require a real
value.

Ken Sheridan
Stafford, England