If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multiple Junction Tables?
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! |
#2
|
|||
|
|||
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 "Barry Moses" wrote in message ... 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! |
#3
|
|||
|
|||
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!!! "Barry Moses" wrote: 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! |
#4
|
|||
|
|||
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 "bmoses" wrote in message ... 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!!! "Barry Moses" wrote: 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! |
#5
|
|||
|
|||
bmoses wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Criterion - How to Write Query for Multiple Tables | jcinn | Running & Setting Up Queries | 1 | February 8th, 2005 12:42 PM |
junction tables for 3 many to many relationships | LAF | Database Design | 17 | January 5th, 2005 04:01 PM |
Help with Junction Tables and Subforms | Maureen Smith | New Users | 11 | September 23rd, 2004 02:39 PM |
updating multiple tables | Sunny | Using Forms | 2 | July 30th, 2004 06:08 PM |
Multiple tables on to one form | LMB | New Users | 4 | May 23rd, 2004 03:35 AM |