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 said:
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: 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?
