Design tables in order to keep history

[snoopy2003]

Hello,

My database contains employee data.
I wish to store historical data of some fields like salary and status
I should be able to display later weekly/monthly/yearly view of the data.

What is the preferred way to design the tables in order to retrieve the data afterward ?

I think to store salary historical data in a separate table like:
workerID
salary
StartDate
EndDate



or
workerID
salary
EffectiveDate

Is it possible to retrieve monthly view of employee salary with this table
(I mean: not all the months appears in the table).

Thank you for your help

[TheShabz]

First off, I would go with your second option, the EffectiveDate. Reason for this being that the start date of one is automatically the end date of the previous, thus making storing the end date redundant information.

As far as viewing in a weekly/monthly/yearly view of salary info, I cant really think of a clean, sexy way of doing it. It would probably come down to running multiple queries with date criteria being "BETWEEN [beginConstraint] AND [endConstraint]". There might be some stock code for this on the web if you have the time to look.

[snoopy2003]

Hello,

Thank you for your answer. I will search for a code example
or VBA sub to handle the calculation and create a table based on the result.

Thank you