Historical tracking of records

[domibud]

I have a db that record employee's transportation expenses.
If an employee being promoted or change department, I'd like for his/her old
transportation expense records locked. Then, the new transportation expense
record will refer to he/she in the new department.

Ex., Employee A in Dept. B being promoted to Dept. C.
To update this, I just need to update my employees table. But if I do this,
all transportation expense records regarding employee A will change
department from B to C. I want all transportation expense record of employee
A when s/he in department B do not change or locked. And the new one will
record employee A transportation expenses in department C.

I'm thinking of adding a new table for storing historical data. If a
promotion/demotion happen, the old record will be copied to the history table
(Expense record and employees record), and will be deleted in the expense
table. The new record than will be stored to the expense table.

Any better idea on how to do this effectively?
I'm using Ms. Access 2003.

 

[Tom van Stiphout]

On Tue, 13 May 2008 20:24:00 -0700, domibud

This is a very common request. The classic example is an invoicing
system, where you store invoices with their line items, each with
their unit price. Prices change over time, but that should not affect
existing invoices. The solution? Store the Price value in a field in
the InvoiceDetails record.
In your case: store the DepartmentID in the Expense record.

-Tom.

 

[a a r o n . k e m p f]

more technically-- it's called a 'slowly changing dimension'-- there
are plenty of reasons to store the changing dimension-- for example--
if you don't have any sales for Product X at Price Y-- you might still
want to understand that you offered this product for 6 months at Price
Y and nobody bought any.

Ralph Kimball has a lot of good stuff about SCD, I'd try to move
upstream to learn more about 'data warehousing'- that is what you're
really talking about.

-Aaron

 

[domibud]

Thanks for all your advice.
For now I'd go with Tom's advice.

--Budi--