Need help with Tables Design and Relationships

I need to come up with a design for the tables listed below. Although I
provided some general information about the relationships
as well, I am not too sure as to how the actual table joins should be
designed.

I truly reply on someone's expertise here (reading between the lines) and
hope to get some ideas how the table
design might work.


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 organization has multiple projects
4. There are multiple tasks that either result from the boards or the
projects
5. Naturally, there are multiple 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 different 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 ideas 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

Hi Tom,

I’ve only been involved with Access/db design for a short time so take what I provide to you as suggestions only, think everything through for yourself, and consider that I’m mistaken. I won’t be able to provide a complete solution to you – just some ideas, OK?

It seems to me that the tblOrganization does not need a ‘relationship’ to all the other tables and trying to map it in only adds to the confusion. Maybe there would be a relationship to map out if there were multiple ‘Organizations’, but as I see it, EVERYthing in your application will be ‘related’ to it. Plus, you can always link the Organization info to the reports (for headers and such).

tblMembers has relationships to everything else (are you mapping out the tables on paper? – if not, I suggest that you do, it really helps me.)

tblTasks has relationships to everything else except skill sets (maybe?).

It seems like each MemberTask at some point will be assigned to none, some, or all of the tables for Board, Project, Priority, and Budget.

However, I suggest that you go through each table and identify its relationship to every other table. Do this one table at a time and ask yourself questions like: “Can a Project EVER be assigned to multiple boards, or is Board: Project = only 1: M?� Only you know the answers to these types of questions and by thinking everything through one relationship at a time, (instead of the entire project) you should be able to map the relationships more easily.

(Samples)
Member: Board = M:M - needs junction table
Member: Project = M:M - needs junction table
Member: Priority = M:M - needs juction table
Member: Budget = ???? - only you know this
Member: Task = M:M - needs junction table
Board: Project = ???? - only you know this
Board: MemberTask = 1: M
Project: MemberTask = 1: M

You already know junction tables, and you should be able to easily identify where they are needed when you review all of your tables. After you've added your junction tables, I suggest that you review all of your tables again and make certain they are properly normalized.

Sorry for the delayed response, but like TC (and probably everyone else here), my time available to log onto here is limited. I found THIS post because you mentioned that you were going to repost on the other thread - usually you should stick to the same thread so others could follow along.

Hope all of this helps you.

rpw




----- Tom wrote: -----

I need to come up with a design for the tables listed below. Although I
provided some general information about the relationships
as well, I am not too sure as to how the actual table joins should be
designed.

I truly reply on someone's expertise here (reading between the lines) and
hope to get some ideas how the table
design might work.


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 organization has multiple projects
4. There are multiple tasks that either result from the boards or the
projects
5. Naturally, there are multiple 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 different 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 ideas 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

Here is ONE way to design this. I have only listed the tables necessary for
the Boards. You would need to extend the same thing for Projects. Keep in
mind that this approach assumes that Boards and Projects are totally
separate. If Boards can have projects then you would have to go a different
route. Also, while you way there is only one organization, this design
allows for multiples, if the need ever arises. Please feel free to post back
with further questions.

tblOrganization

Organization_id PK

...Other organization fields



tblBoard

BoardID PK

OrganizationID FK (to tblOrganization)

..Other Board Fields



tblBoardMembers

BoardMemberID PK

BoardID FK (to tblBoard)

MemberID (do a lookup from tblEmployees)

..Other BoardMember related fields



tblBoardTasks

BoardTaskID PK

BoardID FK (to tblBoard)

..other BoardTask related fields



tblBoardTaskAssignments

BoardTaskAssignmentID PK

BoardTaskID FK (to tblBoardTasks)

AssigneeID (do a lookup from tblEmployees)

...other BoardTaskAssignment related fields

tblEmployees
EmployeeID PK
.....Other Employee related fields

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Tom" wrote in message
...
I need to come up with a design for the tables listed below. Although I
provided some general information about the relationships
as well, I am not too sure as to how the actual table joins should be
designed.

I truly reply on someone's expertise here (reading between the lines) and
hope to get some ideas how the table
design might work.


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 organization has multiple projects
4. There are multiple tasks that either result from the boards or the
projects
5. Naturally, there are multiple 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 different 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 ideas 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

Hi Lynn,

Thanks for jumping in here and diplomatically fixing my errors. ;-)

I have learned some more from the experts once again: Tom never said that Board and Projects were related (my assumption), allow for upgradeability (more than one Organization), 'listen' closer - Tom wanted table design, not relationships.

arggh, go stick foot in mouth...

rpw

----- Lynn Trapp wrote: -----

Here is ONE way to design this. I have only listed the tables necessary for
the Boards. You would need to extend the same thing for Projects. Keep in
mind that this approach assumes that Boards and Projects are totally
separate. If Boards can have projects then you would have to go a different
route. Also, while you way there is only one organization, this design
allows for multiples, if the need ever arises. Please feel free to post back
with further questions.

tblOrganization

Organization_id PK

...Other organization fields



tblBoard

BoardID PK

OrganizationID FK (to tblOrganization)

..Other Board Fields



tblBoardMembers

BoardMemberID PK

BoardID FK (to tblBoard)

MemberID (do a lookup from tblEmployees)

..Other BoardMember related fields



tblBoardTasks

BoardTaskID PK

BoardID FK (to tblBoard)

..other BoardTask related fields



tblBoardTaskAssignments

BoardTaskAssignmentID PK

BoardTaskID FK (to tblBoardTasks)

AssigneeID (do a lookup from tblEmployees)

...other BoardTaskAssignment related fields

tblEmployees
EmployeeID PK
.....Other Employee related fields

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Tom" wrote in message
...
I need to come up with a design for the tables listed below. Although I
provided some general information about the relationships
as well, I am not too sure as to how the actual table joins should be
designed.
I truly reply on someone's expertise here (reading between the lines) and
hope to get some ideas how the table
design might work.
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 organization has multiple projects
4. There are multiple tasks that either result from the boards or the
projects
5. Naturally, there are multiple 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 different 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 ideas 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:

Thanks for your help.... I appreciate all of the info you've provided me
thus far.

Yes, although I'm in need for the relationships as well, it's good to know
that I can probably merge some of the tables.

I will spend some time over the weekend and try to develop this.

I may end up posting more questions. I'd be great if I could ask a few more
questions.

Thanks,
Tom



"rpw" wrote in message
...
Hi Lynn,

Thanks for jumping in here and diplomatically fixing my errors. ;-)

I have learned some more from the experts once again: Tom never said that
Board and Projects were related (my assumption), allow for upgradeability
(more than one Organization), 'listen' closer - Tom wanted table design, not
relationships.

arggh, go stick foot in mouth...

rpw

----- Lynn Trapp wrote: -----

Here is ONE way to design this. I have only listed the tables
necessary for
the Boards. You would need to extend the same thing for Projects.
Keep in
mind that this approach assumes that Boards and Projects are totally
separate. If Boards can have projects then you would have to go a
different
route. Also, while you way there is only one organization, this
design
allows for multiples, if the need ever arises. Please feel free to
post back
with further questions.

tblOrganization

Organization_id PK

...Other organization fields



tblBoard

BoardID PK

OrganizationID FK (to tblOrganization)

..Other Board Fields



tblBoardMembers

BoardMemberID PK

BoardID FK (to tblBoard)

MemberID (do a lookup from tblEmployees)

..Other BoardMember related fields



tblBoardTasks

BoardTaskID PK

BoardID FK (to tblBoard)

..other BoardTask related fields



tblBoardTaskAssignments

BoardTaskAssignmentID PK

BoardTaskID FK (to tblBoardTasks)

AssigneeID (do a lookup from tblEmployees)

...other BoardTaskAssignment related fields

tblEmployees
EmployeeID PK
.....Other Employee related fields

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Tom" wrote in message
...
I need to come up with a design for the tables listed below.
Although I
provided some general information about the relationships
as well, I am not too sure as to how the actual table joins should
be
designed.
I truly reply on someone's expertise here (reading between the
lines) and
hope to get some ideas how the table
design might work.
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 organization has multiple projects
4. There are multiple tasks that either result from the boards or
the
projects
5. Naturally, there are multiple 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 different 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 ideas 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

Lynn:

Thanks for the prompt reply and help in this matter.

I will attempt to design the architecture over the course of the weekend.
If I get stuck on something, I will post another message in this thread...
in hope that you might check it again.

Thanks so much!!! I truly appreciate your feedback.

Tom



"Lynn Trapp" wrote in message
...
Here is ONE way to design this. I have only listed the tables necessary
for
the Boards. You would need to extend the same thing for Projects. Keep in
mind that this approach assumes that Boards and Projects are totally
separate. If Boards can have projects then you would have to go a
different
route. Also, while you way there is only one organization, this design
allows for multiples, if the need ever arises. Please feel free to post
back
with further questions.

tblOrganization

Organization_id PK

..Other organization fields



tblBoard

BoardID PK

OrganizationID FK (to tblOrganization)

.Other Board Fields



tblBoardMembers

BoardMemberID PK

BoardID FK (to tblBoard)

MemberID (do a lookup from tblEmployees)

.Other BoardMember related fields



tblBoardTasks

BoardTaskID PK

BoardID FK (to tblBoard)

.other BoardTask related fields



tblBoardTaskAssignments

BoardTaskAssignmentID PK

BoardTaskID FK (to tblBoardTasks)

AssigneeID (do a lookup from tblEmployees)

..other BoardTaskAssignment related fields

tblEmployees
EmployeeID PK
....Other Employee related fields

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Tom" wrote in message
...
I need to come up with a design for the tables listed below. Although I
provided some general information about the relationships
as well, I am not too sure as to how the actual table joins should be
designed.

I truly reply on someone's expertise here (reading between the lines)
and
hope to get some ideas how the table
design might work.


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 organization has multiple projects
4. There are multiple tasks that either result from the boards or the
projects
5. Naturally, there are multiple 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 different 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 ideas 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,
I wouldn't consider your approach to have "errors". There are always
multiple ways to solve the same problem. I was also confused as to whether
or not Projects might be related to a Board or not. You and I just made
different assumptions. That is why it's important for developers to have
ongoing discussions with the end users.

You can pull your foot out of your mouth...it didn't need to be inserted to
start with. g

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"rpw" wrote in message
...
Hi Lynn,

Thanks for jumping in here and diplomatically fixing my errors. ;-)

I have learned some more from the experts once again: Tom never said that
Board and Projects were related (my assumption), allow for upgradeability
(more than one Organization), 'listen' closer - Tom wanted table design, not
relationships.

arggh, go stick foot in mouth...

rpw

----- Lynn Trapp wrote: -----

Here is ONE way to design this. I have only listed the tables
necessary for
the Boards. You would need to extend the same thing for Projects.
Keep in
mind that this approach assumes that Boards and Projects are totally
separate. If Boards can have projects then you would have to go a
different
route. Also, while you way there is only one organization, this
design
allows for multiples, if the need ever arises. Please feel free to
post back
with further questions.

tblOrganization

Organization_id PK

...Other organization fields



tblBoard

BoardID PK

OrganizationID FK (to tblOrganization)

..Other Board Fields



tblBoardMembers

BoardMemberID PK

BoardID FK (to tblBoard)

MemberID (do a lookup from tblEmployees)

..Other BoardMember related fields



tblBoardTasks

BoardTaskID PK

BoardID FK (to tblBoard)

..other BoardTask related fields



tblBoardTaskAssignments

BoardTaskAssignmentID PK

BoardTaskID FK (to tblBoardTasks)

AssigneeID (do a lookup from tblEmployees)

...other BoardTaskAssignment related fields

tblEmployees
EmployeeID PK
.....Other Employee related fields

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Tom" wrote in message
...
I need to come up with a design for the tables listed below.
Although I
provided some general information about the relationships
as well, I am not too sure as to how the actual table joins should
be
designed.
I truly reply on someone's expertise here (reading between the
lines) and
hope to get some ideas how the table
design might work.
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 organization has multiple projects
4. There are multiple tasks that either result from the boards or
the
projects
5. Naturally, there are multiple 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 different 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 ideas 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

You're quite welcome, Tom. I hope it helps out. I will be sure to monitor
this thread.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Tom" wrote in message
...
Lynn:

Thanks for the prompt reply and help in this matter.

I will attempt to design the architecture over the course of the weekend.
If I get stuck on something, I will post another message in this thread...
in hope that you might check it again.

Thanks so much!!! I truly appreciate your feedback.

Tom



"Lynn Trapp" wrote in message
...
Here is ONE way to design this. I have only listed the tables necessary
for
the Boards. You would need to extend the same thing for Projects. Keep
in
mind that this approach assumes that Boards and Projects are totally
separate. If Boards can have projects then you would have to go a
different
route. Also, while you way there is only one organization, this design
allows for multiples, if the need ever arises. Please feel free to post
back
with further questions.

tblOrganization

Organization_id PK

..Other organization fields



tblBoard

BoardID PK

OrganizationID FK (to tblOrganization)

.Other Board Fields



tblBoardMembers

BoardMemberID PK

BoardID FK (to tblBoard)

MemberID (do a lookup from tblEmployees)

.Other BoardMember related fields



tblBoardTasks

BoardTaskID PK

BoardID FK (to tblBoard)

.other BoardTask related fields



tblBoardTaskAssignments

BoardTaskAssignmentID PK

BoardTaskID FK (to tblBoardTasks)

AssigneeID (do a lookup from tblEmployees)

..other BoardTaskAssignment related fields

tblEmployees
EmployeeID PK
....Other Employee related fields

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Tom" wrote in message
...
I need to come up with a design for the tables listed below. Although
I
provided some general information about the relationships
as well, I am not too sure as to how the actual table joins should be
designed.

I truly reply on someone's expertise here (reading between the lines)
and
hope to get some ideas how the table
design might work.


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 organization has multiple projects
4. There are multiple tasks that either result from the boards or the
projects
5. Naturally, there are multiple 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 different 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 ideas 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

good luck over the weekend and keep asking those questions that you can't ponder out yourself - there's usually someone here that can provide a little bit of guidance

rpw

----- Tom wrote: -----

RPW:

Thanks for your help.... I appreciate all of the info you've provided me
thus far.

Yes, although I'm in need for the relationships as well, it's good to know
that I can probably merge some of the tables.

I will spend some time over the weekend and try to develop this.

I may end up posting more questions. I'd be great if I could ask a few more
questions.

Thanks,
Tom



"rpw" wrote in message
...
Hi Lynn,
Thanks for jumping in here and diplomatically fixing my errors. ;-)
I have learned some more from the experts once again: Tom never said that
Board and Projects were related (my assumption), allow for upgradeability
(more than one Organization), 'listen' closer - Tom wanted table design, not
relationships.
arggh, go stick foot in mouth...
rpw
----- Lynn Trapp wrote: -----
Here is ONE way to design this. I have only listed the tables
necessary for
the Boards. You would need to extend the same thing for Projects.
Keep in
mind that this approach assumes that Boards and Projects are totally
separate. If Boards can have projects then you would have to go a
different
route. Also, while you way there is only one organization, this
design
allows for multiples, if the need ever arises. Please feel free to
post back
with further questions.
tblOrganization
Organization_id PK
...Other organization fields
tblBoard
BoardID PK
OrganizationID FK (to tblOrganization)
..Other Board Fields
tblBoardMembers
BoardMemberID PK
BoardID FK (to tblBoard)
MemberID (do a lookup from tblEmployees)
..Other BoardMember related fields
tblBoardTasks
BoardTaskID PK
BoardID FK (to tblBoard)
..other BoardTask related fields
tblBoardTaskAssignments
BoardTaskAssignmentID PK
BoardTaskID FK (to tblBoardTasks)
AssigneeID (do a lookup from tblEmployees)
...other BoardTaskAssignment related fields
tblEmployees
EmployeeID PK
.....Other Employee related fields
--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
"Tom" wrote in message
...
I need to come up with a design for the tables listed below.
Although I
provided some general information about the relationships
as well, I am not too sure as to how the actual table joins should
be
designed.
I truly reply on someone's expertise here (reading between the
lines) and
hope to get some ideas how the table
design might work.
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 organization has multiple projects
4. There are multiple tasks that either result from the boards or
the
projects
5. Naturally, there are multiple 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 different 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 ideas 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

Lynn:

I had an intial look at this... I followed your recommendations and
developed the tables as suggested.

Here are now some additional questions:

tblBoards:
1. Is tblBoards a "junction table" for tblBoardMembers & tblBoardTasks
2. If yes, should BoardIDPK become an "Autonumber"
3. If yes, do you then recommend to add 2 additional Number fields (Long
Integers) and make all 3 of them composite PKs? Otherwise, can I link two
foreign keys (BoardMemberID and BoardTaskID) to one and the same Autonumber
(BoardIDPK)?

tblEmployees:
1. Is this a "stand-alone" table that is used as a source for MemberIDfk and
AssigneeIDfk (both of the FKs are then Text fields, right?)

Other tables:
1. How do I integrate a) tblProjects, b) tblSkillSets, c) tblPriorities, and
d) tblBudgetCategories into the existing schema?
2. I believe between all of these, there could be a M:M relationship?


I appreciate any additional help on this. And (please) keep in mind that
I'm just learning more about the relational design, so I may ask some
beginner level questions.

Thanks so much in advance, Lynn.


Tom












"Lynn Trapp" wrote in message
...
You're quite welcome, Tom. I hope it helps out. I will be sure to monitor
this thread.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Tom" wrote in message
...
Lynn:

Thanks for the prompt reply and help in this matter.

I will attempt to design the architecture over the course of the
weekend.
If I get stuck on something, I will post another message in this
thread...
in hope that you might check it again.

Thanks so much!!! I truly appreciate your feedback.

Tom



"Lynn Trapp" wrote in message
...
Here is ONE way to design this. I have only listed the tables
necessary
for
the Boards. You would need to extend the same thing for Projects. Keep
in
mind that this approach assumes that Boards and Projects are totally
separate. If Boards can have projects then you would have to go a
different
route. Also, while you way there is only one organization, this design
allows for multiples, if the need ever arises. Please feel free to
post
back
with further questions.

tblOrganization

Organization_id PK

..Other organization fields



tblBoard

BoardID PK

OrganizationID FK (to tblOrganization)

.Other Board Fields



tblBoardMembers

BoardMemberID PK

BoardID FK (to tblBoard)

MemberID (do a lookup from tblEmployees)

.Other BoardMember related fields



tblBoardTasks

BoardTaskID PK

BoardID FK (to tblBoard)

.other BoardTask related fields



tblBoardTaskAssignments

BoardTaskAssignmentID PK

BoardTaskID FK (to tblBoardTasks)

AssigneeID (do a lookup from tblEmployees)

..other BoardTaskAssignment related fields

tblEmployees
EmployeeID PK
....Other Employee related fields

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Tom" wrote in message
...
I need to come up with a design for the tables listed below.
Although
I
provided some general information about the relationships
as well, I am not too sure as to how the actual table joins should
be
designed.

I truly reply on someone's expertise here (reading between the
lines)
and
hope to get some ideas how the table
design might work.


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 organization has multiple projects
4. There are multiple tasks that either result from the boards or
the
projects
5. Naturally, there are multiple 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 different 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 ideas 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