when to use many-many relationships or different data bases
in [Tables]
|
From: RAK on 14 Dec 2009 11:26 I am trying to create an operational database. We hire temporary staff through several different employment agencies to complete work on projects for various clients. The term of employment is linked to individual projects which are time limited and include different assignments within each project. Many of the people we use have worked on many different projects at many different times including several years. I started out thinking I needed 5 different tables and needed to use a many-many relationship. Here are the 5 tables I identified with some types of data: Employee_Personal (usual type data) Employment_History (includes Dates hired, Projects assigned at different times, employment title per project, performance records; rehire status) Agency (related to each employee, # provided for each project; performance of employees provided) Project (Clients, Dates, subjects, teams needed, staff needed) Accounting [(Hours to complete project-administration pre training, training, execution and administrative client reporting); (staff cost factors computed for number of hours *title pay); (computer cost and needs per project); space cost and needs per project)] I started looking at the tables to try to see what relationships I needed and if I needed junction tables. I didn't know how to handle the different dates of employment and different assignments for each employee and wondered if I could use multi-valued fields or value lists for this data. Then I started doubting if I was going in the right direction and thought I'd see if I could get any help.
From: Fred on 14 Dec 2009 12:24 Of course, this needs to be dealt with as a database design issue, not just the linkage aspect. You'll need to decide what the most "granular" level thing you need to database regarding employment for a project. Then instances of that shoudl probably be a table. Most likely this instances of continuous employment of one person for one project.
From: Jeff Boyce on 14 Dec 2009 12:24 The way you handle many-many relationships in Access is via a junction table. It sounds like you are saying that: One Project can have many Tasks. One Task can be assigned to (any-of-many) Staff One Staff can be working for (any-of-many) Agencies Sounds like: [Project] table - info specific to a project [Task] table - Task-specific info, including ProjectID (to which Project does this task belong?) [Agency] table - list of agencies and agency-specific info [Staff] table - AKA [Employee] table [AgencyStaff] table - a junction table, showing who worked for which agency during which time frame [TaskAssignment] table - a junction table, showing which AgencyStaffID worked on which TaskID But you're closer to your situation. Does the above make sense? Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "RAK" <RAK(a)discussions.microsoft.com> wrote in message news:589C60C8-B28D-4D1C-A00A-19DB5F04B55A(a)microsoft.com... >I am trying to create an operational database. We hire temporary staff > through several different employment agencies to complete work on projects > for various clients. The term of employment is linked to individual > projects > which are time limited and include different assignments within each > project. > Many of the people we use have worked on many different projects at many > different times including several years. I started out thinking I needed 5 > different tables and needed to use a many-many relationship. Here are the > 5 > tables I identified with some types of data: > Employee_Personal (usual type data) > Employment_History (includes Dates hired, Projects assigned at different > times, employment title per project, performance records; rehire status) > Agency (related to each employee, # provided for each project; performance > of employees provided) > Project (Clients, Dates, subjects, teams needed, staff needed) > Accounting [(Hours to complete project-administration pre training, > training, execution and administrative client reporting); (staff cost > factors > computed for number of hours *title pay); (computer cost and needs per > project); space cost and needs per project)] > > I started looking at the tables to try to see what relationships I needed > and if I needed junction tables. I didn't know how to handle the different > dates of employment and different assignments for each employee and > wondered > if I could use multi-valued fields or value lists for this data. Then I > started doubting if I was going in the right direction and thought I'd see > if > I could get any help. >
From: Dorian on 14 Dec 2009 12:38 Only you can determine how things relate to eachh other since you know your business rules. I can envisage an employee working for multiple agencies possible more than one simultaneously. Whether this could happen during a project you would know. An employee might work on more than one project simultaneously possibly for different agencies. Actually it seems like you are handling several distinct functions in one database. You have an Employee system, a Project system and an Accounting system. Will these all be used by the same people. Possibly you may need multiple front-ends for different functions. Of course, since I dont know your systems this may all be inaccurate. -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". "RAK" wrote: > I am trying to create an operational database. We hire temporary staff > through several different employment agencies to complete work on projects > for various clients. The term of employment is linked to individual projects > which are time limited and include different assignments within each project. > Many of the people we use have worked on many different projects at many > different times including several years. I started out thinking I needed 5 > different tables and needed to use a many-many relationship. Here are the 5 > tables I identified with some types of data: > Employee_Personal (usual type data) > Employment_History (includes Dates hired, Projects assigned at different > times, employment title per project, performance records; rehire status) > Agency (related to each employee, # provided for each project; performance > of employees provided) > Project (Clients, Dates, subjects, teams needed, staff needed) > Accounting [(Hours to complete project-administration pre training, > training, execution and administrative client reporting); (staff cost factors > computed for number of hours *title pay); (computer cost and needs per > project); space cost and needs per project)] > > I started looking at the tables to try to see what relationships I needed > and if I needed junction tables. I didn't know how to handle the different > dates of employment and different assignments for each employee and wondered > if I could use multi-valued fields or value lists for this data. Then I > started doubting if I was going in the right direction and thought I'd see if > I could get any help. >
From: rolaaus on 15 Dec 2009 03:11
One question that I have, after reading your post is when you mention "how to handle the different dates of employment and different assignments for each employee". Here's my question. Does your system *need* to link the dates of a project with the dates of an employees start/end date? In other words, let's say for examples sake, that you have an employee that started working for your company on Feb 1, 2008. He was assigned to a project that happened to have started on Jan 1, 2008. You didn't mentioned tasks, but another person did, so there's another factor. What if you have one employee that starts a task/project and isn't able to complete it for whatever reason (fired, quits, it doesn't matter why) and you have to hire someone new. This is why I ask the question, does your system need to keep track of employees start/end dates, and !directly link! them to other dates (projects or tasks) in the system? Usually project tracking systems are really going to care. But if this system is going to be used by the "bean counters" (accountants and/or executive level - VP, etc) then it probably will matter, a lot! "RAK" wrote: > I am trying to create an operational database. We hire temporary staff > through several different employment agencies to complete work on projects > for various clients. The term of employment is linked to individual projects > which are time limited and include different assignments within each project. > Many of the people we use have worked on many different projects at many > different times including several years. I started out thinking I needed 5 > different tables and needed to use a many-many relationship. Here are the 5 > tables I identified with some types of data: > Employee_Personal (usual type data) > Employment_History (includes Dates hired, Projects assigned at different > times, employment title per project, performance records; rehire status) > Agency (related to each employee, # provided for each project; performance > of employees provided) > Project (Clients, Dates, subjects, teams needed, staff needed) > Accounting [(Hours to complete project-administration pre training, > training, execution and administrative client reporting); (staff cost factors > computed for number of hours *title pay); (computer cost and needs per > project); space cost and needs per project)] > > I started looking at the tables to try to see what relationships I needed > and if I needed junction tables. I didn't know how to handle the different > dates of employment and different assignments for each employee and wondered > if I could use multi-valued fields or value lists for this data. Then I > started doubting if I was going in the right direction and thought I'd see if > I could get any help. > |