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 Many-To-Many Tables
Hello,
I am familiar with using a junction table for a "many-to-many" relationship. However, I'm not exactly sure how I could string multiple "many-to-many" or junction tables into an architecture. Does anyone have a suggestion? For instance, I may have members that are assigned to many projects (and each project many members). But I also have boards that have multiple priorities and multiple priorities are linked to multiple organizations. Currently, I'm myself trying to figure out the relationships between all the entities. Again, knowing how I could use (with a generic example) multiple junction tables would be great advice. Thanks, Tom |
#2
|
|||
|
|||
Multiple Many-To-Many Tables
"Tom" wrote in message ... Hello, I am familiar with using a junction table for a "many-to-many" relationship. However, I'm not exactly sure how I could string multiple "many-to-many" or junction tables into an architecture. Does anyone have a suggestion? Here are two examples. 1. If A is many-to-many (m:m) with B, you need a junction table AB (or whatever) - as you know. Similarly, if some other table C is m:m to D, you need another junction table CD. Ditto for as many other tables & junction pairs that are required. 2. Say you have Person, Club, and PersonClub. (The latter is the junction table to allow a person to belong to many clubs, and a club to have many persons.) Say you need to record each consecutive period that a person was a member of each club. (Eg. 1990-1993, 1998-2001 etc). In this case, for each one entry in the PersonClub table, there are many entries in a table of date-ranges: tblPersonClub PersonID ( composite ) ClubID ( primary key ) DateRange PersonID ( composite ) ClubID ( primary ) DateFrom ( key ) DateTo In the latter example: - Person : Club is m:m ) the normal - Person : PersonClub is 1:m ) junction table - Club : PersonClub is 1:m ) structure - PersonClub : DateRange is 1:m Do those help? For instance, I may have members that are assigned to many projects (and each project many members). But I also have boards that have multiple priorities and multiple priorities are linked to multiple organizations. Currently, I'm myself trying to figure out the relationships between all the entities. Again, knowing how I could use (with a generic example) multiple junction tables would be great advice. Thanks, Tom |
#3
|
|||
|
|||
Multiple Many-To-Many Tables
TC:
Thanks for the info. Not sure if I entirely understand your schema. I got the 3 tables: tblPersonClub is the junction which has 2 Fields (Long Integer) that are linked to the primary keys of the other 2 tables: tblPerson & tblClub So here are my questions: 1. What did you mean by "composite"? 2. What is the "DateRange". I assume it's a field, right? 3. ClubID is the primary but DateFrom = "key"... not sure what you mean by that? 4. Also, I thought I would end up with 6 tables. 2 pairs of 2 tables linked via their junction tables. And then link the 2 junction tables to one another... I'm probably wrong about this, right? Thanks for any additional info, Tom "TC" wrote in message ... "Tom" wrote in message ... Hello, I am familiar with using a junction table for a "many-to-many" relationship. However, I'm not exactly sure how I could string multiple "many-to-many" or junction tables into an architecture. Does anyone have a suggestion? Here are two examples. 1. If A is many-to-many (m:m) with B, you need a junction table AB (or whatever) - as you know. Similarly, if some other table C is m:m to D, you need another junction table CD. Ditto for as many other tables & junction pairs that are required. 2. Say you have Person, Club, and PersonClub. (The latter is the junction table to allow a person to belong to many clubs, and a club to have many persons.) Say you need to record each consecutive period that a person was a member of each club. (Eg. 1990-1993, 1998-2001 etc). In this case, for each one entry in the PersonClub table, there are many entries in a table of date-ranges: tblPersonClub PersonID ( composite ) ClubID ( primary key ) DateRange PersonID ( composite ) ClubID ( primary ) DateFrom ( key ) DateTo In the latter example: - Person : Club is m:m ) the normal - Person : PersonClub is 1:m ) junction table - Club : PersonClub is 1:m ) structure - PersonClub : DateRange is 1:m Do those help? For instance, I may have members that are assigned to many projects (and each project many members). But I also have boards that have multiple priorities and multiple priorities are linked to multiple organizations. Currently, I'm myself trying to figure out the relationships between all the entities. Again, knowing how I could use (with a generic example) multiple junction tables would be great advice. Thanks, Tom |
#4
|
|||
|
|||
Multiple Many-To-Many Tables
Hi Tom,
it's been a while since TC answered, so I'll jump in here Comments in-line... Not sure if I entirely understand your schema. I got the 3 tables: tblPersonClub is the junction which has 2 Fields (Long Integer) that are linked to the primary keys of the other 2 tables: tblPerson & tblClub So here are my questions: 1. What did you mean by "composite"? Instead of one field of the table being designated as the PK, you can select multiple fields and designate the combination as a PK (hence "composite" ) It is difficult to illustrate here, but the intent is that two or three fields are grouped together as a "composite primary key" Try looking at TC's original post again with that in mind. 2. What is the "DateRange". I assume it's a field, right? I believe he meant DateRange as a table (maybe tblDateRange is better) and the sub-listings are the fields. 3. ClubID is the primary but DateFrom = "key"... not sure what you mean by that? See the answer to number one above. 4. Also, I thought I would end up with 6 tables. 2 pairs of 2 tables linked via their junction tables. And then link the 2 junction tables to one another... I'm probably wrong about this, right? TC's four table sample and explanation should be sufficient to accomplish what you asked for - stringing multiple m:m relationships together. Look at his tables/relations again: tblPerson (has 1:m to tblPersonClub) tblClub (has 1:m to tblPersonClub) tblPersonClub (is the normal junction table structure) tblDateRange (has 1:m to tblPersonClub) In your first post you said: For instance, I may have members that are assigned to many projects (and each project many members). But I also have boards that have multiple priorities and multiple priorities are linked to multiple organizations. For what you state specifically, you could have these tables: tblMembers tblProjects tblMemberProjects and tblBoards tblOrganizations tblBoardOrganization (or tblPriorities - this is your junction table) Do you also want m:m Members to Boards, m:m Members to Organization, and m:m Members to Priorities? (you didn't say that's what you wanted, so I'm just guessing here) Do Projects have Priorities too? Knowing what you know, what do you think you would need to handle the m:m relationships listed here? hope this helps you rpw Thanks for any additional info, Tom "TC" wrote in message ... "Tom" wrote in message ... Hello, I am familiar with using a junction table for a "many-to-many" relationship. However, I'm not exactly sure how I could string multiple "many-to-many" or junction tables into an architecture. Does anyone have a suggestion? Here are two examples. 1. If A is many-to-many (m:m) with B, you need a junction table AB (or whatever) - as you know. Similarly, if some other table C is m:m to D, you need another junction table CD. Ditto for as many other tables & junction pairs that are required. 2. Say you have Person, Club, and PersonClub. (The latter is the junction table to allow a person to belong to many clubs, and a club to have many persons.) Say you need to record each consecutive period that a person was a member of each club. (Eg. 1990-1993, 1998-2001 etc). In this case, for each one entry in the PersonClub table, there are many entries in a table of date-ranges: tblPersonClub PersonID ( composite ) ClubID ( primary key ) DateRange PersonID ( composite ) ClubID ( primary ) DateFrom ( key ) DateTo In the latter example: - Person : Club is m:m ) the normal - Person : PersonClub is 1:m ) junction table - Club : PersonClub is 1:m ) structure - PersonClub : DateRange is 1:m Do those help? For instance, I may have members that are assigned to many projects (and each project many members). But I also have boards that have multiple priorities and multiple priorities are linked to multiple organizations. Currently, I'm myself trying to figure out the relationships between all the entities. Again, knowing how I could use (with a generic example) multiple junction tables would be great advice. Thanks, Tom |
#5
|
|||
|
|||
Multiple Many-To-Many Tables
RPW:
Thanks for "jumping in" and providing me feedback. Okay, I have attempted to recreate the table design that TC suggested. I'm not too sure though if I understand the concept of the "spliced in" tblDates. As of now, the junction table (tblPersonClub) has only 2 fields: 1. PersonID (Number; Long Integer) 2. ClubID (Number; Long Integer) Both of these fields are primary keys (or I should say now composite keys, right?). In tblDateRange, I have the following fields: 1. ClubID 2. PersonID 3. DateFrom what are the data types of the 1st two fields? Are they also "Numbers; Long Integers" or is one of them an "Autonumber". Currently, no matter how I set them, I get the "Indeterminate Relationship" in the Edit Relationship Window so I can not join the tblDateRange into the junction table. Okay, once I got this to work, I somehow need to get the actual table design to work as well. Here, I really believe I need some serious help. I'm completely lost (and don't have all the information either to provide the Newsgroups w/ some real information... ugh). Hopefully, I could rely on some of your expertise (and interpretion) as to how the design can be achieved. Here are the tables: - Organization - Boards - Employees - Tasks - Priorities - Projects - Budget Categories - Skill Sets Again, I don't have all the exact information myself as to how the tables should be joined. Maybe, there's even a chance that I don't need all the tables listed above. But, let me try to provide some more information: Organization: 1. There is 1 organization 2. The organization has multiple boards 3. The organizaton has multiple projects 4. There are multiple tasks that either result from the boards or the projects 5. Naturally, there are multipe members in the organization that can be assigned to a) boards, b) projects ) boards and projects Boards: 1. Multiple boards will have multiple members 2. As a result of board meetings, there will be multiple tasks 3. These tasks will have different priorities 4. The tasks will be subsidized via different funds (budget categories) Employees: 1. Multiple employees can be assigned to multiple boards 2. Multiple employees can be assigned to multiple projects 3. All employees have differnt skills sets (multiple skills sets) 4. The employees will be assigned to different tasks Budget Categories, Skill Sets have been listed in the description above. Not sure how else they could be further described here. Again, if I could get some ideas as to how best construct this, I would really appreciate it!!! Any help would really help me in my job. THANKS! Please don't hesitate to post additional questions if I didn't provide sufficient information. Thanks so much, Tom "rpw" wrote in message news Hi Tom, it's been a while since TC answered, so I'll jump in here Comments in-line... Not sure if I entirely understand your schema. I got the 3 tables: tblPersonClub is the junction which has 2 Fields (Long Integer) that are linked to the primary keys of the other 2 tables: tblPerson & tblClub So here are my questions: 1. What did you mean by "composite"? Instead of one field of the table being designated as the PK, you can select multiple fields and designate the combination as a PK (hence "composite" ) It is difficult to illustrate here, but the intent is that two or three fields are grouped together as a "composite primary key" Try looking at TC's original post again with that in mind. 2. What is the "DateRange". I assume it's a field, right? I believe he meant DateRange as a table (maybe tblDateRange is better) and the sub-listings are the fields. 3. ClubID is the primary but DateFrom = "key"... not sure what you mean by that? See the answer to number one above. 4. Also, I thought I would end up with 6 tables. 2 pairs of 2 tables linked via their junction tables. And then link the 2 junction tables to one another... I'm probably wrong about this, right? TC's four table sample and explanation should be sufficient to accomplish what you asked for - stringing multiple m:m relationships together. Look at his tables/relations again: tblPerson (has 1:m to tblPersonClub) tblClub (has 1:m to tblPersonClub) tblPersonClub (is the normal junction table structure) tblDateRange (has 1:m to tblPersonClub) In your first post you said: For instance, I may have members that are assigned to many projects (and each project many members). But I also have boards that have multiple priorities and multiple priorities are linked to multiple organizations. For what you state specifically, you could have these tables: tblMembers tblProjects tblMemberProjects and tblBoards tblOrganizations tblBoardOrganization (or tblPriorities - this is your junction table) Do you also want m:m Members to Boards, m:m Members to Organization, and m:m Members to Priorities? (you didn't say that's what you wanted, so I'm just guessing here) Do Projects have Priorities too? Knowing what you know, what do you think you would need to handle the m:m relationships listed here? hope this helps you rpw Thanks for any additional info, Tom "TC" wrote in message ... "Tom" wrote in message ... Hello, I am familiar with using a junction table for a "many-to-many" relationship. However, I'm not exactly sure how I could string multiple "many-to-many" or junction tables into an architecture. Does anyone have a suggestion? Here are two examples. 1. If A is many-to-many (m:m) with B, you need a junction table AB (or whatever) - as you know. Similarly, if some other table C is m:m to D, you need another junction table CD. Ditto for as many other tables & junction pairs that are required. 2. Say you have Person, Club, and PersonClub. (The latter is the junction table to allow a person to belong to many clubs, and a club to have many persons.) Say you need to record each consecutive period that a person was a member of each club. (Eg. 1990-1993, 1998-2001 etc). In this case, for each one entry in the PersonClub table, there are many entries in a table of date-ranges: tblPersonClub PersonID ( composite ) ClubID ( primary key ) DateRange PersonID ( composite ) ClubID ( primary ) DateFrom ( key ) DateTo In the latter example: - Person : Club is m:m ) the normal - Person : PersonClub is 1:m ) junction table - Club : PersonClub is 1:m ) structure - PersonClub : DateRange is 1:m Do those help? For instance, I may have members that are assigned to many projects (and each project many members). But I also have boards that have multiple priorities and multiple priorities are linked to multiple organizations. Currently, I'm myself trying to figure out the relationships between all the entities. Again, knowing how I could use (with a generic example) multiple junction tables would be great advice. Thanks, Tom |
#6
|
|||
|
|||
Multiple Many-To-Many Tables
Yes, you got me spot-on :-)
I can only get on the web once a day, & it gets difficult towards week-ends, cos I have other obligations. Cheers, TC "rpw" wrote in message news Hi Tom, it's been a while since TC answered, so I'll jump in here Comments in-line... Not sure if I entirely understand your schema. I got the 3 tables: tblPersonClub is the junction which has 2 Fields (Long Integer) that are linked to the primary keys of the other 2 tables: tblPerson & tblClub So here are my questions: 1. What did you mean by "composite"? Instead of one field of the table being designated as the PK, you can select multiple fields and designate the combination as a PK (hence "composite" ) It is difficult to illustrate here, but the intent is that two or three fields are grouped together as a "composite primary key" Try looking at TC's original post again with that in mind. 2. What is the "DateRange". I assume it's a field, right? I believe he meant DateRange as a table (maybe tblDateRange is better) and the sub-listings are the fields. 3. ClubID is the primary but DateFrom = "key"... not sure what you mean by that? See the answer to number one above. 4. Also, I thought I would end up with 6 tables. 2 pairs of 2 tables linked via their junction tables. And then link the 2 junction tables to one another... I'm probably wrong about this, right? TC's four table sample and explanation should be sufficient to accomplish what you asked for - stringing multiple m:m relationships together. Look at his tables/relations again: tblPerson (has 1:m to tblPersonClub) tblClub (has 1:m to tblPersonClub) tblPersonClub (is the normal junction table structure) tblDateRange (has 1:m to tblPersonClub) In your first post you said: For instance, I may have members that are assigned to many projects (and each project many members). But I also have boards that have multiple priorities and multiple priorities are linked to multiple organizations. For what you state specifically, you could have these tables: tblMembers tblProjects tblMemberProjects and tblBoards tblOrganizations tblBoardOrganization (or tblPriorities - this is your junction table) Do you also want m:m Members to Boards, m:m Members to Organization, and m:m Members to Priorities? (you didn't say that's what you wanted, so I'm just guessing here) Do Projects have Priorities too? Knowing what you know, what do you think you would need to handle the m:m relationships listed here? hope this helps you rpw Thanks for any additional info, Tom "TC" wrote in message ... "Tom" wrote in message ... Hello, I am familiar with using a junction table for a "many-to-many" relationship. However, I'm not exactly sure how I could string multiple "many-to-many" or junction tables into an architecture. Does anyone have a suggestion? Here are two examples. 1. If A is many-to-many (m:m) with B, you need a junction table AB (or whatever) - as you know. Similarly, if some other table C is m:m to D, you need another junction table CD. Ditto for as many other tables & junction pairs that are required. 2. Say you have Person, Club, and PersonClub. (The latter is the junction table to allow a person to belong to many clubs, and a club to have many persons.) Say you need to record each consecutive period that a person was a member of each club. (Eg. 1990-1993, 1998-2001 etc). In this case, for each one entry in the PersonClub table, there are many entries in a table of date-ranges: tblPersonClub PersonID ( composite ) ClubID ( primary key ) DateRange PersonID ( composite ) ClubID ( primary ) DateFrom ( key ) DateTo In the latter example: - Person : Club is m:m ) the normal - Person : PersonClub is 1:m ) junction table - Club : PersonClub is 1:m ) structure - PersonClub : DateRange is 1:m Do those help? For instance, I may have members that are assigned to many projects (and each project many members). But I also have boards that have multiple priorities and multiple priorities are linked to multiple organizations. Currently, I'm myself trying to figure out the relationships between all the entities. Again, knowing how I could use (with a generic example) multiple junction tables would be great advice. Thanks, Tom |
#7
|
|||
|
|||
Multiple Many-To-Many Tables
TC,
anyhow, I appreciate your feedback so far. I'll go ahead and post another thread in hope to get more advice. -- Thanks, Tom "TC" wrote in message ... Yes, you got me spot-on :-) I can only get on the web once a day, & it gets difficult towards week-ends, cos I have other obligations. Cheers, TC "rpw" wrote in message news Hi Tom, it's been a while since TC answered, so I'll jump in here Comments in-line... Not sure if I entirely understand your schema. I got the 3 tables: tblPersonClub is the junction which has 2 Fields (Long Integer) that are linked to the primary keys of the other 2 tables: tblPerson & tblClub So here are my questions: 1. What did you mean by "composite"? Instead of one field of the table being designated as the PK, you can select multiple fields and designate the combination as a PK (hence "composite" ) It is difficult to illustrate here, but the intent is that two or three fields are grouped together as a "composite primary key" Try looking at TC's original post again with that in mind. 2. What is the "DateRange". I assume it's a field, right? I believe he meant DateRange as a table (maybe tblDateRange is better) and the sub-listings are the fields. 3. ClubID is the primary but DateFrom = "key"... not sure what you mean by that? See the answer to number one above. 4. Also, I thought I would end up with 6 tables. 2 pairs of 2 tables linked via their junction tables. And then link the 2 junction tables to one another... I'm probably wrong about this, right? TC's four table sample and explanation should be sufficient to accomplish what you asked for - stringing multiple m:m relationships together. Look at his tables/relations again: tblPerson (has 1:m to tblPersonClub) tblClub (has 1:m to tblPersonClub) tblPersonClub (is the normal junction table structure) tblDateRange (has 1:m to tblPersonClub) In your first post you said: For instance, I may have members that are assigned to many projects (and each project many members). But I also have boards that have multiple priorities and multiple priorities are linked to multiple organizations. For what you state specifically, you could have these tables: tblMembers tblProjects tblMemberProjects and tblBoards tblOrganizations tblBoardOrganization (or tblPriorities - this is your junction table) Do you also want m:m Members to Boards, m:m Members to Organization, and m:m Members to Priorities? (you didn't say that's what you wanted, so I'm just guessing here) Do Projects have Priorities too? Knowing what you know, what do you think you would need to handle the m:m relationships listed here? hope this helps you rpw Thanks for any additional info, Tom "TC" wrote in message ... "Tom" wrote in message ... Hello, I am familiar with using a junction table for a "many-to-many" relationship. However, I'm not exactly sure how I could string multiple "many-to-many" or junction tables into an architecture. Does anyone have a suggestion? Here are two examples. 1. If A is many-to-many (m:m) with B, you need a junction table AB (or whatever) - as you know. Similarly, if some other table C is m:m to D, you need another junction table CD. Ditto for as many other tables & junction pairs that are required. 2. Say you have Person, Club, and PersonClub. (The latter is the junction table to allow a person to belong to many clubs, and a club to have many persons.) Say you need to record each consecutive period that a person was a member of each club. (Eg. 1990-1993, 1998-2001 etc). In this case, for each one entry in the PersonClub table, there are many entries in a table of date-ranges: tblPersonClub PersonID ( composite ) ClubID ( primary key ) DateRange PersonID ( composite ) ClubID ( primary ) DateFrom ( key ) DateTo In the latter example: - Person : Club is m:m ) the normal - Person : PersonClub is 1:m ) junction table - Club : PersonClub is 1:m ) structure - PersonClub : DateRange is 1:m Do those help? For instance, I may have members that are assigned to many projects (and each project many members). But I also have boards that have multiple priorities and multiple priorities are linked to multiple organizations. Currently, I'm myself trying to figure out the relationships between all the entities. Again, knowing how I could use (with a generic example) multiple junction tables would be great advice. Thanks, Tom |
#8
|
|||
|
|||
Multiple Many-To-Many Tables
Thanks Tom. Good luck with your quest!
TC "Tom" wrote in message ... TC, anyhow, I appreciate your feedback so far. I'll go ahead and post another thread in hope to get more advice. -- Thanks, Tom "TC" wrote in message ... Yes, you got me spot-on :-) I can only get on the web once a day, & it gets difficult towards week-ends, cos I have other obligations. Cheers, TC "rpw" wrote in message news Hi Tom, it's been a while since TC answered, so I'll jump in here Comments in-line... Not sure if I entirely understand your schema. I got the 3 tables: tblPersonClub is the junction which has 2 Fields (Long Integer) that are linked to the primary keys of the other 2 tables: tblPerson & tblClub So here are my questions: 1. What did you mean by "composite"? Instead of one field of the table being designated as the PK, you can select multiple fields and designate the combination as a PK (hence "composite" ) It is difficult to illustrate here, but the intent is that two or three fields are grouped together as a "composite primary key" Try looking at TC's original post again with that in mind. 2. What is the "DateRange". I assume it's a field, right? I believe he meant DateRange as a table (maybe tblDateRange is better) and the sub-listings are the fields. 3. ClubID is the primary but DateFrom = "key"... not sure what you mean by that? See the answer to number one above. 4. Also, I thought I would end up with 6 tables. 2 pairs of 2 tables linked via their junction tables. And then link the 2 junction tables to one another... I'm probably wrong about this, right? TC's four table sample and explanation should be sufficient to accomplish what you asked for - stringing multiple m:m relationships together. Look at his tables/relations again: tblPerson (has 1:m to tblPersonClub) tblClub (has 1:m to tblPersonClub) tblPersonClub (is the normal junction table structure) tblDateRange (has 1:m to tblPersonClub) In your first post you said: For instance, I may have members that are assigned to many projects (and each project many members). But I also have boards that have multiple priorities and multiple priorities are linked to multiple organizations. For what you state specifically, you could have these tables: tblMembers tblProjects tblMemberProjects and tblBoards tblOrganizations tblBoardOrganization (or tblPriorities - this is your junction table) Do you also want m:m Members to Boards, m:m Members to Organization, and m:m Members to Priorities? (you didn't say that's what you wanted, so I'm just guessing here) Do Projects have Priorities too? Knowing what you know, what do you think you would need to handle the m:m relationships listed here? hope this helps you rpw Thanks for any additional info, Tom "TC" wrote in message ... "Tom" wrote in message ... Hello, I am familiar with using a junction table for a "many-to-many" relationship. However, I'm not exactly sure how I could string multiple "many-to-many" or junction tables into an architecture. Does anyone have a suggestion? Here are two examples. 1. If A is many-to-many (m:m) with B, you need a junction table AB (or whatever) - as you know. Similarly, if some other table C is m:m to D, you need another junction table CD. Ditto for as many other tables & junction pairs that are required. 2. Say you have Person, Club, and PersonClub. (The latter is the junction table to allow a person to belong to many clubs, and a club to have many persons.) Say you need to record each consecutive period that a person was a member of each club. (Eg. 1990-1993, 1998-2001 etc). In this case, for each one entry in the PersonClub table, there are many entries in a table of date-ranges: tblPersonClub PersonID ( composite ) ClubID ( primary key ) DateRange PersonID ( composite ) ClubID ( primary ) DateFrom ( key ) DateTo In the latter example: - Person : Club is m:m ) the normal - Person : PersonClub is 1:m ) junction table - Club : PersonClub is 1:m ) structure - PersonClub : DateRange is 1:m Do those help? For instance, I may have members that are assigned to many projects (and each project many members). But I also have boards that have multiple priorities and multiple priorities are linked to multiple organizations. Currently, I'm myself trying to figure out the relationships between all the entities. Again, knowing how I could use (with a generic example) multiple junction tables would be great advice. Thanks, Tom |
Thread Tools | |
Display Modes | |
|
|