Project and Employee Relationship

[Jason]

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.

 

[Allen Browne]

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.

 

[Jason]

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

 

[Allen Browne]

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.