How to prevent the calculation structure when a field value is revised?

[jerryg72]

I have a database of employees with their salary and other perks included. When I make their salary slip each month the amount is calculated based on the basic pay and their perks which includes housing allowance, transportation allowance and couple other allowances. When I make a change on any of the allowances from now onwards the calculation is affected for the previous months as well. How can I prevent this happening. How can the previous months pay slip stay with the previous allowance as it was and the new calculation affects from the current month onwards? Thank you.

[xps35]

There are mainly two ways.
The first is to store for each month the amounts that are actually paid per Employee.
The other method is to make a table where you store all changes in allowances, so you will know which amounts applies in a certain month.

[CJ_London]

I would add to xps35's other method that the (history of) allowances table should contain an 'effective from' date. Your payslip would then look up the the allowance which was in effect on the date of the payslip.

My own preference when you have a legal document (such as a payslip, sales invoice etc) is to store all the values that apply to that document. That way it can be reproduced accurately at any time in the future regardless of any modifications to the underlying data.

I may well still maintain a history table as well as it might be relevant for other reports, contain additional information of interest such as why the value is changing, perhaps a link to an authorisation, etc. An example of this might be basic pay. An employee changes role to one on a different pay grade midway through a pay period, so part of the basic pay for the pay period is on one rate, and part on another.

[NoellaG]

third method: generate a PDF when new pay slips are generated and store the pdf's. For some legal documents this might be required.