Keeping Line Manager information up to date

[adgesap]

I look after a database for my HR department. One of the most difficult areas we have to keep up to date is the Line Manager - "Direct Report" relationship.
As is (I believe) fairly standard, each employee has on their Jobs table (one to many with main table) a field for recording who their line manager is.
But what happens is if a line manager leaves, that leaves a number of 'orphaned' direct reports, who might not get a replacement for a week to several months.
Help, any suggestions on the problem, or alternative ways of looking at it would be really useful

 

[johnherman]

Instead of organizing by person/employee, perhaps the "report to" should go by position/title, i.e. like an org chart. A little more complicated and a lot more flexible.

EmployeeTable
EmployeeID
FirstName
LastName
etc.

PositionTable
PositionID
PositionTitle (or this may belong in the table below)
PositionReportsTo (foreign key to PositionID, self join)
etc.

EmployeePositionTable
EmployeePositionID (unique identifier)
EmployeeID (foreign key to EmployeeTable)
PositionID (foreign key to PositionTable)
StartDate
EndDate
Salary
etc.










-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright