If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Need help with Tables Design and Relationships
(the sound of foot being extracted - deep breath - "aaaaah") Thank you for that, Lynn. :-) (note to self - learned a little more) ----- Lynn Trapp wrote: ----- 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 |
#12
|
|||
|
|||
Need help with Tables Design and Relationships
snork
-- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "rpw" wrote in message ... (the sound of foot being extracted - deep breath - "aaaaah") Thank you for that, Lynn. :-) (note to self - learned a little more) ----- Lynn Trapp wrote: ----- 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 |
#13
|
|||
|
|||
Need help with Tables Design and Relationships
Answers inline below
-- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Tom" wrote in message ... 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 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 |
#14
|
|||
|
|||
Need help with Tables Design and Relationships
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 when I use a single field 'autonumber' PK. ----- Tom wrote: ----- 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 |
#15
|
|||
|
|||
Need help with Tables Design and Relationships
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 "Lynn Trapp" wrote in message ... Answers inline below -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Tom" wrote in message ... 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 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 |
#16
|
|||
|
|||
Need help with Tables Design and Relationships
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 "rpw" wrote in message ... 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. ----- Tom wrote: ----- 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 |
#17
|
|||
|
|||
Need help with Tables Design and Relationships
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 -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Tom" wrote in message ... 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 "Lynn Trapp" wrote in message ... Answers inline below -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Tom" wrote in message ... 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 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 |
#18
|
|||
|
|||
Need help with Tables Design and Relationships
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? -- Thanks, Tom "Lynn Trapp" wrote in message ... 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 -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Tom" wrote in message ... 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 "Lynn Trapp" wrote in message ... Answers inline below -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Tom" wrote in message ... 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 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 |
#19
|
|||
|
|||
Need help with Tables Design and Relationships
Tom,
Why are you trying to select EVERY field in ALL 6 tables???? -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Tom" wrote in message ... 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? -- Thanks, Tom "Lynn Trapp" wrote in message ... 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 -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Tom" wrote in message ... 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 "Lynn Trapp" wrote in message ... Answers inline below -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Tom" wrote in message ... 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 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 |
#20
|
|||
|
|||
Need help with Tables Design and Relationships
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? -- Thanks, Tom "Lynn Trapp" wrote in message ... Tom, Why are you trying to select EVERY field in ALL 6 tables???? -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Tom" wrote in message ... 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? -- Thanks, Tom "Lynn Trapp" wrote in message ... 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 -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Tom" wrote in message ... 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 "Lynn Trapp" wrote in message ... Answers inline below -- Lynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm "Tom" wrote in message ... 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 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 |
Thread Tools | |
Display Modes | |
|
|