Need help with Tables Design and Relationships

[Tom]

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]

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 tabl
Member: Project = M:M - needs junction tabl
Member: Priority = M:M - needs juction tabl
Member: Budget = ???? - only you know thi
Member: Task = M:M - needs junction tabl
Board: Project = ???? - only you know thi
Board: MemberTask = 1:
Project: MemberTask = 1:

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

rp



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

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

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


Here are the tables

- Organizatio
- Board
- Employee
- Task
- Prioritie
- Project
- Budget Categorie
- Skill Set

Again, I don't have all the exact information myself as to how the table
should be joined. Maybe, there's even a chance that I don't need all th
tables listed above

But, let me try to provide some more information

Organization
1. There is 1 organizatio
2. The organization has multiple board
3. The organization has multiple project
4. There are multiple tasks that either result from the boards or th
project
5. Naturally, there are multiple members in the organization that can b
assigned to a) boards, b) projects ) boards and project

Boards
1. Multiple boards will have multiple member
2. As a result of board meetings, there will be multiple task
3. These tasks will have different prioritie
4. The tasks will be subsidized via different funds (budget categories

Employees
1. Multiple employees can be assigned to multiple board
2. Multiple employees can be assigned to multiple project
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 Trapp]

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

 

[rpw]

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..

rp

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

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

tblOrganizatio

Organization_id P

...Other organization field



tblBoar

BoardID P

OrganizationID FK (to tblOrganization

..Other Board Field



tblBoardMember

BoardMemberID P

BoardID FK (to tblBoard

MemberID (do a lookup from tblEmployees

..Other BoardMember related field



tblBoardTask

BoardTaskID P

BoardID FK (to tblBoard

..other BoardTask related field



tblBoardTaskAssignment

BoardTaskAssignmentID P

BoardTaskID FK (to tblBoardTasks

AssigneeID (do a lookup from tblEmployees

...other BoardTaskAssignment related field

tblEmployee
EmployeeID P
.....Other Employee related field

 

[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

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.

 

[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]

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

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.

 

[Lynn Trapp]

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

 

[rpw]

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 guidanc

rp

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

RPW

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

Yes, although I'm in need for the relationships as well, it's good to kno
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 mor
questions

Thanks
To

Hi Lynn

Board and Projects were related (my assumption), allow for upgradeabilit
(more than one Organization), 'listen' closer - Tom wanted table design, no
relationships

 

[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]

<snork>

 

[Lynn Trapp]

Answers inline below

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

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

No, tblBoardMembers is a "junction table" between tblBoards and tblEmployees

2. If yes, should BoardIDPK become an "Autonumber"

It can be or you can use some natural key instead

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)?

I don't think you have quite understood what I'm after here. I will try to
take the time to draw out a better ER diagram and get it to you next week.

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?)
Yes


Other tables:
1. How do I integrate a) tblProjects, b) tblSkillSets, c) tblPriorities, and
d) tblBudgetCategories into the existing schema?

I would see Skill sets as a subset of the Employee records and Priorities
and BudgetCategories as subsets of Projects

 

[rpw]

Tom

Looks like Lynn is going to spend a considerable amount of time over the weekend working on your problem with these tables. Lucky for you

After reading your question #3, it seems like the concept of 'composite' keys is not quite solidified for you. So I'm going to review some basic information and maybe by the end of it everything will have a little more clarity. (And please don't take offense if it is over-simplified - I don't really know what you do and don't know.)

As you know, every table should have a primary key (PK) and this (PK) could be an 'Autonumber'. Of course, using an 'autonumber' (PK) insures that each record ID is unique. Also, as you have learned, 'autonumbers' are long-integer by default. When you refer to a (PK) from a different table it is typically identified as a foreign key (FK). The field that holds the (FK) must be of the same data type as the (PK) for Access to allow the link between the two tables. This means that your (FK) field must be long-integer if it relates to an 'autonumber' (PK)

Now, when you have a 'junction table' like TC suggested in your earlier thread, you can have a 'composite primary key'. You know that this 'junction table' needs a primary key, but that doesn't necessarily mean that there must be one field designated for that purpose - it could be a combination of fields. On the other hand, just because a table has foreign keys, that doesn't mean that you really need a 'composite PK' made up with thoses (FK)'s

I'm going to use an unlikey example for illustrative purposes. Imagine three tables; tblNames, tblCity, tblAddress. Now imagine another 'junction table' called tblNCA. This last table might have the following fields

tblNC
ncaID (PK
NameID (FK
CityID (FK
AddressID (FK

With these fields, the table functions very well - there is a unique primary key and the foriegn keys link the information from the other tables to the NCA table

Now,let's assume that we did not want a single field PK and we are going to combine the (FK) fields into a 'composite PK'. The resultant table might look like this

tblNC
NameID (FK) (These
CityID (FK) (fields combine to mak
AddressID (FK) (the 'composite' P

This table could also function very well

I sorta figured that with your line of thinking, you would set up the table this way

tblNC
ncaID (Having al
NameID (FK) (four of these field
CityID (FK) (combined into a
AddressID (FK) (single 'composite PK'

When TC suggested using a combination of foriegn keys as a 'composite primary key', he determined that the combination of two unique ID numbers was unique enough that there would likely never be duplication. In those cases, a separate 'autonumber' field is not necessary. So while this last table is functional, it does have an extra and unnecessary ncaID field

In your question #3 you asked: "and make all 3 of them composite PKs?". Not quite, there is still only one (PK) even though it is made up of multiple (FK)'s

I hope this helps you along

rp

btw, in all of my 5 months of using Access I still haven't needed or wanted to use 'composite PK's. It's usually easier for me to understand when I use a single field 'autonumber' PK


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

Lynn

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

Here are now some additional questions

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

tblEmployees
1. Is this a "stand-alone" table that is used as a source for MemberIDfk an
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

 

[Tom]

Lynn:

Again, thanks for the feedback. Please bear with me on this.

Okay, I made some small progress but I'm still uncertain about the
relationship between
tblEmployees and tblBoardTaskAssignments.

tblEmployees:
Pk: MemberID (Text data type); 1:M to MemberIDfk of tblBoardMembers (that's
okay right now)
AssigneeID (Text data type)

tblBoardTaskAssignments:
Pk: BoardTaskAssignmentsID
AssigneeID (Text data type)


My Questions:
1. Right now, when creating the join between tblEmployees.AssigneeID &
tblBoardTaskAssignments.AssigneeID,
I get an "Indeterminate Relationship" error? What am I doing wrong? Does
either of the AssigneeID needs
to become a composite PK?

2. When you mentioned that Skills set should be a "subset" of the Employee
records, did you mean that "Skill Set"
will be a field of the tblEmployees or did you mean that tblSkillSet is a
child table of tblEmployees?

3. Just in Q2, will priorities & budget categories become fields of
tblProjects or will they become tables themselves?



BTW, I appreciate your mentioning to provide me an ER diagram? I believe
that really would help me better understand
your design.


Again, I truly appreciate your help in this.


Thanks in advance,
Tom

 

[Tom]

RPW:

Thanks again! These Newsgroups are simply awesome... I'm learning a lot
here and I always "run into" very helpful individuals, like you or Lynn.

Anyhow, I appreciate your feedback and will review it further. I just
posted an additional thread to Lynn's last reply.

It seems like I'm getting there, although slowly... please bear w/ me....
still learning.

Again, I'll review your feedback (later on tonight or tomorrow morning) and
post my replies to you then. Thanks so much for your help.

--
Thanks,
Tom

Tom,

Looks like Lynn is going to spend a considerable amount of time over the

weekend working on your problem with these tables. Lucky for you!

After reading your question #3, it seems like the concept of 'composite'

keys is not quite solidified for you. So I'm going to review some basic
information and maybe by the end of it everything will have a little more
clarity. (And please don't take offense if it is over-simplified - I don't
really know what you do and don't know.)

As you know, every table should have a primary key (PK) and this (PK)

could be an 'Autonumber'. Of course, using an 'autonumber' (PK) insures
that each record ID is unique. Also, as you have learned, 'autonumbers' are
long-integer by default. When you refer to a (PK) from a different table it
is typically identified as a foreign key (FK). The field that holds the
(FK) must be of the same data type as the (PK) for Access to allow the link
between the two tables. This means that your (FK) field must be
long-integer if it relates to an 'autonumber' (PK).

Now, when you have a 'junction table' like TC suggested in your earlier

thread, you can have a 'composite primary key'. You know that this
'junction table' needs a primary key, but that doesn't necessarily mean that
there must be one field designated for that purpose - it could be a
combination of fields. On the other hand, just because a table has foreign
keys, that doesn't mean that you really need a 'composite PK' made up with
thoses (FK)'s.

I'm going to use an unlikey example for illustrative purposes. Imagine

three tables; tblNames, tblCity, tblAddress. Now imagine another 'junction
table' called tblNCA. This last table might have the following fields:

tblNCA
ncaID (PK)
NameID (FK)
CityID (FK)
AddressID (FK)

With these fields, the table functions very well - there is a unique

primary key and the foriegn keys link the information from the other tables
to the NCA table.

Now,let's assume that we did not want a single field PK and we are going

to combine the (FK) fields into a 'composite PK'. The resultant table might
look like this:

tblNCA
NameID (FK) (These 3
CityID (FK) (fields combine to make
AddressID (FK) (the 'composite' PK

This table could also function very well.

I sorta figured that with your line of thinking, you would set up the table this way:

tblNCA
ncaID (Having all
NameID (FK) (four of these fields
CityID (FK) (combined into a
AddressID (FK) (single 'composite PK'

When TC suggested using a combination of foriegn keys as a 'composite

primary key', he determined that the combination of two unique ID numbers
was unique enough that there would likely never be duplication. In those
cases, a separate 'autonumber' field is not necessary. So while this last
table is functional, it does have an extra and unnecessary ncaID field.

In your question #3 you asked: "and make all 3 of them composite PKs?".

Not quite, there is still only one (PK) even though it is made up of
multiple (FK)'s.

I hope this helps you along.

rpw

btw, in all of my 5 months of using Access I still haven't needed or

wanted to use 'composite PK's. It's usually easier for me to understand whe
n I use a single field 'autonumber' PK.

 

[Lynn Trapp]

Tom,
I think I may have led you astray with my terminology. You dont' want the
AssigneeID in Both of those tables. Rather, you need to lookup the MemberID
from the Employees table and store it in the AssigneeID of the
BoardTaskAssignments table. Look at the modified layout below

BoardTaskAssignmentID PK
BoardTaskID FK (to tblBoardTasks)
AssigneeID (do a lookup of EmployeeID from tblEmployees)
...other BoardTaskAssignment related fields

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

 

[Tom]

Lynn:

Again, thanks for the feedback. I made the changes accordingly. All
relationships are joined properly (I believe) in the relationship view.

In query view, however, when I select every field of all 6 tables and then
execute the query, "nothing" shows up.

What I mean by "nothing" is that I don't even see a blank record. I simply
see the gray field labels. In the past, this normally was an indication
for me that something was not properly joined.

Assuming that I have created the relationships as you suggested, should I
expect to see "nothing" in the query that pulls all fields from all tables.
I hope my terminology makes sense?

 

[Lynn Trapp]

Tom,
Why are you trying to select EVERY field in ALL 6 tables????

 

[Tom]

I have been doing this for testing purposes. I was under the impression
that running such "testing query" would determine whether or the
relationship between all tables and fields are joined properly.

The way you sound, this is not a valid process for checking the
relationships, right?

 

[Lynn Trapp]

Yes, it is a valid process, but I would start much simpler first. Just query
a couple of tables first. Then, when you are sure it is giving you the right
data, add another, etc. That will, hopefully, lead you to realize where the
connections are incorrect.