Results 1 to 10 of 10
Dec 9, 2009, 15:07 #1
- Join Date
- Dec 2009
- 0 Post(s)
- 0 Thread(s)
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:
CREATE TABLE trades ( id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, tradeid INTEGER UNSIGNED, stockid INTEGER UNSIGNED, price DECIMAL(10, 4), quantity INTEGER UNSIGNED, ... ... status VARCHAR(10), 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.