stored vs calculated values

[dan-cat]

Hello All,

Ok I'm still a little rusty as to when and why you shouldn't store a value.

Take, for example a db designed to process timesheets.
Timesheet form details employee, rate of pay, total hours and total pay
Timesheet continious sub-form details date, starttime, finishtime and total hours worked for the day.

Now I'm assuming that the total hours field and the rate of pay field(taken from a seperate employee table) within the timesheet form should not be stored in the timesheet table but calculated.

I understand the principle behind it because it avoids bloating and duplication.

However what happens if the user goes into the employee table and changes the rate of pay for a specific employee? All the historic records will be recalculated with the new rates of pay.

Is there a point where storing values is preferable to calculating?

Thankyou

Dan

 

[dangermouse]

In this situation i would archive the data that is calculated to an archive table with the details for reference later. this is what i had to do on a wages database i wrote. it helps for wage queries later

 

[neileg]

Where your calculation is dependent on data that may change, and where the historical value of the calculation is required, then you must store this value. Otherwise, you can't recreate the values.

 

[dan-cat]

Thanks for the response. I've been through many threads on this forum where individuals have been advised not to store data because of duplication etc but I wasn't sure whether this practice had to be strictly adhered to

 

[Pat Hartman]

We also tell you when it is appropriate to store values from other tables. It is almost never appropriate to store calculated values, particularly if the calculation is based on fields from the same row in the same table.

Since pay rate is time dependent, it is acceptable to store it with the hours recorded. A more technically correct method is to store pay rate in its own table, keyed by employeeID and startDate. EndDate is also stored to facilitate searches. Then whenever you need to perform a calculation involving PayRate, you would use the transaction date to find the PayRate for that period.

 

[dan-cat]

Thanks All,

Yes I see that it would be better to store a time period for each employees pay rate. That way you gain a stored history of an employees pay rate and can then perform calculations from there.

Ensuring calculated fields are not stored really scrutinises the integrity of your db structure.