Inquiry: Junction Tables

[Eric Tubbs]

Greetings,

I would like to inquire about how to correctly set up a junction table to
handle a many-to-many relationship. Since Access cant handle this type of
situation, it needs a third table to break the many-to-many into two
one-to-many relationships.

Below are two scenarios and would like your input please.

Example 1: Assigning a primary key for the junction table

tblOrganization
organizationID (pk)
addressID (fk to tblAddress)

tblDepartments
deptID (pk)
deptNmTxt

tblUserDepartment -- Junction Table
userDeptID (pk)
deptID (fk to tblDepartments)
userID (fk to tblUser)

tblUser
userID (pk)
userNmTxt

tblUserRole -- Junction Table
userRoleID (pk)
userID (fk to tblUser)
roleID (fk to tblRole)

tblRoleID
roleID (pk)
roleNmTxt

tblAddress
addressID (pk)

tblEmployee
employeeID (fk)
addressID (fk to tblAddress)
userID (fk to tblUser)

Example 2: Using a multi-primary key for the junction table from the other
two tables

tblUserDepartment -- Junction Table
deptID (fk to tblDepartments)
userID (fk to tblUser)

tblUserRole -- Junction Table
userID (fk to tblUser)
roleID (fk to tblRole)

Which one of these examples (or both) would be acceptable? Is one method
preferred over the other? If so why?

Many TIA's,

Eric

 

[Michel Walsh]

Hi,


I would use the surrogate key for the junction table, but keep the
UNIQUE constraint on the pair of foreign keys too.

Why? Just because it allows me to simplify many SQL statements, since
neither Jet, neither MS SQL Server, allow "vector" comparison :



WHERE (f1, f2) IN ( SELECT g1, g2 FROM ... )


If you have a compound pk, and need to reach a given record, you have to
change the IN above into an EXISTS:

WHERE EXISTS ( SELECT * FROM ... WHERE f1=g1 AND f2=g2)


which, if you add aliases and the whole real syntax, become easily much
"heavier" to maintain.

Having a surrogate key (or, in fact, a key made of a single field) do
not spawn that kind of problems, and the IN clause above can be kept, in its
simplicity.


I keep the unique constraint since I don't want multiple paths in my
many-to-many relation, which would made INNER JOIN quite horrible, and
UNIQUE has to be each of the one-to-one relation, anyhow.



Hoping it may help,
Vanderghast, Access MVP

 

[Eric Tubbs]

Michel,

Thanks for the suggestion and a little background information. Your feedback
will help me further down the road where I will eventually learn how to
write sql queries.

Is the table, tblUser, really needed at all if one was to implement Access
security? I have read through the Access security document once already and
it seems to me that I could eliminate it and substitute the table,
tblEmployee, for the tblUser? Would this be ok?

I have a question regarding your statement: "I keep the unique constraint
since I don't want multiple paths in my many-to-many relation, which would
made INNER JOIN quite horrible, and UNIQUE has to be each of the one-to-one
relation, anyhow."

What do you mean by the one-to-one relation? Shouldnt that be a one-to-many
relationship? What are you referring to?

Thanks for the wonderful explanation,

Eric

 

[Michel Walsh]

Hi,

If you want to implement Access (Jet) security, Jet will maintain the Users
(and Groups) in its own structures and you do not need them, for Security,
at least. You may need them for other reasons, but not for Security.


I should have not mentioned a one-to-one relation in this context, indeed,
it creates more fuzziness than otherwise. No, what I was referring to, is,
for example, if I have qualifications on one side, and employees, on the
other, then, I do not want, in my junction table, something like:

pk employeeID qualificationID
1010, John, Can weld aluminum
1011, John, Can weld aluminum


The couple (employeeID, qualificationID) must keep its constraint of being
UNIQUE, so the second record would not have been accepted.

Seen like that, I also have to add, in general, a constraint of not null on
employeeID and qualificationID too.


Hoping it may help,
Vanderghast, Access MVP




A UNIQUE constraint on "something else" than the PK can be "seen" as a kind
of one-to-one relation between that "something else", and the PK, as long as
that "something else" does not allow nulls values.

 

[Eric Tubbs]

Michel,

Your explanation was wonderful and I was able to understand it. Thanks for
clarifying the key points.

Thanks,

Eric