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
|
|||
|
|||
Project and Employee Relationship
We are creating a project database to monitor our
projects. For every project, there are 2 employee records linked - one for who is working for that project and the other one is who is supervising the project. The one who is working for the project can also be the one who is supervising the project. In the Project form, we have two items, WorkEmployeeID and SupEmployeeID. In the Employee table, we would like to show the projects on hand for every Employee. For instance, the Project A is assigned to Jack Lee for both working and supervising. We would like to show the project twice in the project subform in the Employee Form. That is: Jack Lee Project A Working Project A Supervising Project B Supervising Project C Supervising What is the best way to achieve this relationship? Is it a good idea to add a field W/S in the Employee table to show he / she is working / supervising the project? How can we update that field when we select the EmployeeID in the Project main form ? Thank you for your help. |
#2
|
|||
|
|||
It might be best with 4 tables:
- Project table: one record for each project, with a ProjectID primary key; - Employee table: one record for each person, with an EmployeeID primary key; - Role table: one record for each kind of role a person can have in a project (e.g. Supervising, Working); The 4th table contains one record for each person associated with a project, and defines their role in the project. The fields will look like this: ProjectID foreign key to Project.ProjectID EmployeeID foreign key to Employee.EmployeeID RoleID foreign key to Role.RoleID StartDate the date the employee was given this role in this project. Now you can have a main form bound to the employee table, with a subform bound to the 4th table. It shows the projects the employee is associated with. You can also create a main form bound to the project table, with a subform bound to the 4th table. The subform shows the employees associated iwth the project. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Jason" wrote in message ... We are creating a project database to monitor our projects. For every project, there are 2 employee records linked - one for who is working for that project and the other one is who is supervising the project. The one who is working for the project can also be the one who is supervising the project. In the Project form, we have two items, WorkEmployeeID and SupEmployeeID. In the Employee table, we would like to show the projects on hand for every Employee. For instance, the Project A is assigned to Jack Lee for both working and supervising. We would like to show the project twice in the project subform in the Employee Form. That is: Jack Lee Project A Working Project A Supervising Project B Supervising Project C Supervising What is the best way to achieve this relationship? Is it a good idea to add a field W/S in the Employee table to show he / she is working / supervising the project? How can we update that field when we select the EmployeeID in the Project main form ? Thank you for your help. |
#3
|
|||
|
|||
Thank you for your advice.
However, since one of the staff is going to leave in 3 months' time. If I delete that record, it will affect a number of projects overseen by him (The project will be assigned to other staff, I suppose). I worry whether there is referential integrity problem when I delete that staff. Thanks -----Original Message----- It might be best with 4 tables: - Project table: one record for each project, with a ProjectID primary key; - Employee table: one record for each person, with an EmployeeID primary key; - Role table: one record for each kind of role a person can have in a project (e.g. Supervising, Working); The 4th table contains one record for each person associated with a project, and defines their role in the project. The fields will look like this: ProjectID foreign key to Project.ProjectID EmployeeID foreign key to Employee.EmployeeID RoleID foreign key to Role.RoleID StartDate the date the employee was given this role in this project. Now you can have a main form bound to the employee table, with a subform bound to the 4th table. It shows the projects the employee is associated with. You can also create a main form bound to the project table, with a subform bound to the 4th table. The subform shows the employees associated iwth the project. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Jason" wrote in message ... We are creating a project database to monitor our projects. For every project, there are 2 employee records linked - one for who is working for that project and the other one is who is supervising the project. The one who is working for the project can also be the one who is supervising the project. In the Project form, we have two items, WorkEmployeeID and SupEmployeeID. In the Employee table, we would like to show the projects on hand for every Employee. For instance, the Project A is assigned to Jack Lee for both working and supervising. We would like to show the project twice in the project subform in the Employee Form. That is: Jack Lee Project A Working Project A Supervising Project B Supervising Project C Supervising What is the best way to achieve this relationship? Is it a good idea to add a field W/S in the Employee table to show he / she is working / supervising the project? How can we update that field when we select the EmployeeID in the Project main form ? Thank you for your help. . |
#4
|
|||
|
|||
If you delete the record from the 4th table, you are not losing the
employee, or the project--only the record that this employee was involved in this project. There is no referential integrity problem. If different people are involved at different times, and you want to keep record of that, you could add a yes/no field named (say) Inactive to the 4th table. Instead of deleting the record, just mark it inactive. You can still cound the number of active records for any employee at any time. Alternatively, you could include a StartDate and EndDate in table 4 to specify the date range when the employee is working in a role on a project. This gives a better history, and also allows you to query the number of projects a person is working on at any time in the past or the future. You probably need EndDate to be open-ended (Null if no termination date is anticipated), whereas StartDate is required. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Jason" wrote in message ... Thank you for your advice. However, since one of the staff is going to leave in 3 months' time. If I delete that record, it will affect a number of projects overseen by him (The project will be assigned to other staff, I suppose). I worry whether there is referential integrity problem when I delete that staff. Thanks -----Original Message----- It might be best with 4 tables: - Project table: one record for each project, with a ProjectID primary key; - Employee table: one record for each person, with an EmployeeID primary key; - Role table: one record for each kind of role a person can have in a project (e.g. Supervising, Working); The 4th table contains one record for each person associated with a project, and defines their role in the project. The fields will look like this: ProjectID foreign key to Project.ProjectID EmployeeID foreign key to Employee.EmployeeID RoleID foreign key to Role.RoleID StartDate the date the employee was given this role in this project. Now you can have a main form bound to the employee table, with a subform bound to the 4th table. It shows the projects the employee is associated with. You can also create a main form bound to the project table, with a subform bound to the 4th table. The subform shows the employees associated iwth the project. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Jason" wrote in message ... We are creating a project database to monitor our projects. For every project, there are 2 employee records linked - one for who is working for that project and the other one is who is supervising the project. The one who is working for the project can also be the one who is supervising the project. In the Project form, we have two items, WorkEmployeeID and SupEmployeeID. In the Employee table, we would like to show the projects on hand for every Employee. For instance, the Project A is assigned to Jack Lee for both working and supervising. We would like to show the project twice in the project subform in the Employee Form. That is: Jack Lee Project A Working Project A Supervising Project B Supervising Project C Supervising What is the best way to achieve this relationship? Is it a good idea to add a field W/S in the Employee table to show he / she is working / supervising the project? How can we update that field when we select the EmployeeID in the Project main form ? Thank you for your help. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Employee Emergency | Don | Running & Setting Up Queries | 28 | February 28th, 2005 05:07 PM |
Designing and employee vacation / holiday database | Chris Strug | New Users | 1 | November 9th, 2004 06:05 PM |
Design questions for tracking employee hrs & client billing | JPearson | Database Design | 3 | May 24th, 2004 06:06 AM |