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 |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Need help with Tables Design and Relationships
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 |
#3
|
|||
|
|||
Need help with Tables Design and Relationships
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 |
#4
|
|||
|
|||
Need help with Tables Design and Relationships
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 |
#5
|
|||
|
|||
Need help with Tables Design and Relationships
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 |
#6
|
|||
|
|||
Need help with Tables Design and Relationships
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 |
#7
|
|||
|
|||
Need help with Tables Design and Relationships
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 |
#8
|
|||
|
|||
Need help with Tables Design and Relationships
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 |
#9
|
|||
|
|||
Need help with Tables Design and Relationships
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 |
#10
|
|||
|
|||
Need help with Tables Design and Relationships
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 |
Thread Tools | |
Display Modes | |
|
|