Best practice:how to save history?

[eluser]rogierb[/eluser]
Hello you wonderfully smart people,

I have a huge html table with all kind of calculated data. The calculations are mostly dictated by law. The data can be manipulated a bit and when the user is satisfied, part of it get's saved.

The saved data can then be proccesed by someone else.

Now here is the problem, people tend to f*ck up the data due to numerous reasons(mainly stupidity) and this causes some minor problems (lawsuits and such)

So now I have to store everything. After thinking about it for quite some time, I can't seem to come up with a decent solution. I want to be able to use all the data for future reference. If in a years time I want to know what has been calculated, and show it on screen in the same format as a new calculation would look. I do not want law changes or code changes to interfere with previous calculations.

So how would I go about? store it in the db, plain html, pdf?

[eluser]Cro_Crx[/eluser]
If you're storing data that constantly changes then I would suggest using a database (as that is what they are designed to do). Wiki's currently do a similar sort of thing to what you are suggesting, so that users can view the history and see what users have edited what.

To achieve what you want, the best way I can think of would be to store specifically the changes that a user performs each time anything is updated. Then separately store the completed version of the data (so that it can be read quickly).

Alternatively you could store the entire set of data at each update, but this would waste a lot of space which large amounts of data, as you would need to store an entire copy of the data each time the user changes anything eg. fixes a single letter spelling error for example.

There's already plenty of free software out there that would do exactly what you want if you didn't want to write something yourself. There are many free wiki applications that can be used publicly/privately to achive this. See http://en.wikipedia.org/wiki/Comparison_...i_software

So yeah, you could do either of the two ways. OR just don't allow your users to edit the data directly

Good luck.

[eluser]attos[/eluser]
You might have a workflow or process problem. Why do people change the calculated values?
You might need to address this by requiring a third party to authorize any changes. You should also need to create an audit log with the changes made, who made them and who authorised them. This should be done by triggers in the database when records are inserted or updated. Also some security needs to be added to the database by not allowing anybody to modify the data directly.
One more thing that can be done is not to allow the modification of a record once it in the database. If the values need to be calculated again you would create a new record that overrides the previous values and link them. In this way you have the history of how the values were calculated.
We normally use four audit fields for each record in the database. One for who created the record, another with the timestamp of when it was created, another with the user that last modified it and the last with the timestamp of when it was modified. These four fields are populated by triggers when the record is inserted or updated.

[eluser]rogierb[/eluser]
Hi Guys,

I need to clarify this a bit more. This system calculates wages, holidays, sickdays and such.
The reason someone (a manager) can change this is because the initital data(which hours are worked, hours taken off etc.) can be superceded or corrected. So on the fly, values are recalculated. Once all changes are made, a subset of thing are saved(everything we need by law to do a payout) The problem the information gathered by the manager may be faulty, input may be wrong, employees 'forget' to record hours or breaks. All kind of problems.
But once a final version is saved, only the supermanager can overrule it, just before payout. Once the payout thas commenced, no changes can be made.


So there are 3 stages (one isn't mentioned) I need to save the entire set of data, but only for viewing. All database transactions are saved regardless.
I think the easiest way is to generate a HTML file wich can be displayed and a PDF as backup. I don't need the data, I only need to show it for future reference.

As for workflow and authorisation that is not a problem. Unfortunately the problem lies in human error...