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