Table set-up that allows for audit trail
I have a question how to set-up a table that allows for different versions for each item.
Let say I have a table containing stock trades and I want to keep the old record if a trade gets changed.
Currently my solution is as follows:
I create a trade in a two-step procedure, where in the initial INSERT step I leave tradeid NULL and status 'Temp', and in the second UPDATE step I set tradeid equal to id and status 'Live'. This is version 1.
CREATE TABLE trades (
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
tradeid INTEGER UNSIGNED,
stockid INTEGER UNSIGNED,
price DECIMAL(10, 4),
quantity INTEGER UNSIGNED,
version INTEGER UNSIGNED
Now when a trade gets changed, I take the following steps:
- UPDATE the status of the old record to 'Changed'
- INSERT a new record with an auto-incremented id, but the tradeid of the original record. This 'version 2' record will contain the changes and gets 'Live' status.
Above works so far, but I am wondering if there is a better way of doing this. I assume this can be fitted in a more general framework, which perhaps has a best-practice solution.