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