Multiple Junction Tables?
in [Tables]
|
From: Barry Moses on 22 Sep 2005 06:15 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!
From: Jeff Boyce on 22 Sep 2005 08:48 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" <bemoses(a)sbcglobal.net> wrote in message news:eiFxa61vFHA.2728(a)TK2MSFTNGP14.phx.gbl... > 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! > >
From: bmoses on 22 Sep 2005 11:26 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! > > >
From: Jeff Boyce on 22 Sep 2005 13:03 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" <bmoses(a)discussions.microsoft.com> wrote in message news:ABD20B89-2AF6-401A-BCD0-8EDA21151E30(a)microsoft.com... > 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! >> >> >>
From: peregenem on 23 Sep 2005 03:52 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
|
Pages: 1 Prev: Cascade Update & Delete Next: how to create a question answer form? |