storing point in time values in a table

  • Thread starter Thread starter Muppet
  • Start date Start date
M

Muppet

What is a good way to approach a "point in time" design
for a lookup table and its relation to a transaction table?

I have a Call table and an Employee table (I have more but
I think this will suffice for the purposes of explantion).

tblCall
EmployeeID
TimeOfCall
'other details

tblEmployee
EmployeeID
FirstName
LastName
Department

Now let's say that when I take a call, I assign EmployeeID.

Further let's say that the employee moves to a different
depeartment in 6 months.

When I run a select query in 6 months, the call will
reflect the CURRENT department information. However, I
want the call to reflect the POINT IN TIME department.

What is the best way to handle this?
 
Muppet,

In essence, it would involve taking the Department field out of the
tblEmployee table, and put it in another table where you track the
employee's history...
Table: EmploymentHistory
EmploymentHistoryID
EmployeeID
StartDate
Department

That way, you will be able to work out in a query which department the
employee was in at the date of the Call in question.
 
Thanks.

Steve Schapel said:
Muppet,

In essence, it would involve taking the Department field out of the
tblEmployee table, and put it in another table where you track the
employee's history...
Table: EmploymentHistory
EmploymentHistoryID
EmployeeID
StartDate
Department

That way, you will be able to work out in a query which department the
employee was in at the date of the Call in question.
Click to expand...
 
Back
Top