Multiple Junction Tables?

[Barry Moses]

I have several tables that require many-to-many relationships; Would you
create one Junction Table holding the PK's of all the other tables.....or
would you make several Junction Tables?

What I am trying to determine is why you would have several Junction
Tables...by looking at Sample DB's.

Also, what is the purpose of making the combined PK's in the Junction Table
out of the other Tables?

Thanks for your reponses!

 

[Jeff Boyce]

Barry

To resolve many-to-many relationships, use a junction
(relationship/resolver/...) table. However, unless ALL of your tables have
m-m relationships with ALL of your tables, you wouldn't want to use a single
junction table. Instead, use one junction table per m-m relationship. In
many cases, there are pairs of tables joined m-m, so you'd use one junction
table per pair. In one particularly complex relationship, I used a junction
table to show four m-m tables' relationships.

One approach to ensuring uniqueness in the junction table is to make your
primary key span the foreign keys from the related (m-m) tables. Another
approach is to create a unique identifier for that row (e.g., an autonumber
primary key), and add an index (No duplicates) spanning the foreign keys.

I've used the former approach if the junction table is, itself, in a m-m
relationship with other tables -- it was easier (for me) to use the junction
table's single field ID than to propagate the underlying foreign keys into
the subsequent junction tables (but you may also get responses pointing out
that propagating the keys will make joins/queries easier).

Regards

Jeff Boyce
<Access MVP>

 

[bmoses]

Jeff. Thanks for your response. If I may impose a bit further. I'm so
close to grasping this cloud in my head..

On the same issue of multiple junction tables.

Table 1 is my primary table. MEMBERS
Table 2 TRAINING (sessions)
Table 3 PRACTCE (sessions)
Table 4 INCIDENTS (sessions)
Table 5 JUNCTION (has ALL Primary keys from the above tables)
I actually have a few more but to minimize the thread...

A member can attend several of each of the other tables....
And each of the other tables can contain multiple members.

What would be the 'proper' way to handle the junction table(s)?

Also, when using forms..(sorry:-()), I may add a new member and want to
attach him to a practice session that already exists... Suggestions? I'm
having difficulty determining when to add infomration to a junction table (if
at all?), i.e., how they are populated.

and thank you so, so much!!!

 

[Jeff Boyce]

Barry

One thing at a time -- first the tables, then work on forms...

If the "real world" situation you are trying to model allows one Member to
attend zero-to-many TRAININGS (and you have multiple trainings), you need a
junction table to resolve the m-m.

If the "real world" ... allows one Member to be connected with zero-to-many
PRACTICES (and you have ...), you need a junction table FOR THIS
relationship.

If ... a MEMBER can be connected with 0-m INCIDENTS (and you have ...), you
need a junction table FOR THIS relationship.

NOTES:

"The other tables" cannot contain members -- you need the junction table(s).

I can't tell from your post whether TRAININGS, PRACTICES and INCIDENTS are
in any way related to each other.

Regards

Jeff Boyce
<Access MVP>

 

[peregenem]

A member can attend several of each of the other tables....
And each of the other tables can contain multiple members.

What would be the 'proper' way to handle the junction table(s)?

Suggested reading:

http://www.intelligententerprise.com/010101/celko.jhtml