Empoyee Changes Department

[Guest]

I have a database/normalization design issue:

A client has requested that I desigin a relational database to keep track of
jobs, work orders, employees and time cards.

The employee table has a field called "deptid" which is a FK of the
department table.

What happens when the employee changes deptartments? I could simply change
the dept field, but then all past historical data would appear as if the
empoyee worked in the current dept, and not the previous dept.

Any suggestions?

 

[Allen Browne]

This is a design choice.

If you don't care about storing the history, and one person is never shared
between departments, then the design you have sounds ideal.

If you need to maintain a history, or if employees can be part time in
different dept's, you need a related table with fields like these:
EmployeeID foreign key to Employee.EmployeeID
DeptID fk to Dept.DeptID
StartDate when this person started at this level in this dept
EndDate when this person ceased at this level in this dept
FullTimeEqiv percentage, such as 0.5 for half-time.

The last field might be something else such as HoursPerWeek, depending on
what your HR dept wants.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

"(e-mail address removed)"

 

[Guest]

Thanks so much!

This is a design choice.

If you don't care about storing the history, and one person is never shared
between departments, then the design you have sounds ideal.

If you need to maintain a history, or if employees can be part time in
different dept's, you need a related table with fields like these:
EmployeeID foreign key to Employee.EmployeeID
DeptID fk to Dept.DeptID
StartDate when this person started at this level in this dept
EndDate when this person ceased at this level in this dept
FullTimeEqiv percentage, such as 0.5 for half-time.

The last field might be something else such as HoursPerWeek, depending on
what your HR dept wants.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

"(e-mail address removed)"