May 15th, 2004, 03:47 AM
|
|
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
|