Table set-up that allows for audit trail

[rblon]

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
)

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.

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.

[IBazz]

I would try making the process a one step thing and add a date of 0000-00-00 to a new column. then, when a trade changes, update the date to that of when the trade changed.

So, if the date shows as 0000-00-00, it is current. if it is 2009-12-10, it was changed on that date. so you can 'decypher' the current trades based on the date value AND/OR you can see when it was changed by the date value.

That should mean you can enter the data to the first tbale and use last_insert_id to get the ID value for trade_id in the second table. No need then (I think) for status column.

bazz

[oddz]

trades
-------------
id (primary key)
...
created_on_timestamp

trade_versions
---------------
id (primary key)
trades_id (foreign key => trades.id)
...
created_on_timestamp

The version can be determined by the timestamp.

You shouldn't need to update the old row or most recent version. Instead you should always insert a new row with the new data into versions table and account for this throughout the rest of the application code.

You can even make things a little easier on yourself and add a deleted column to the version table. The active versions deleted column will be NULL while the older versions are the TIMESTAMP for when the version was deleted. Then your joins to find the most recent version (or if one exists) are just a matter of joining on trades_id and delete IS NULL.

SELECT
     t.id AS trades_id
     ,COALESCE(tv.price,t.price) AS price
     ,COALESCE(tv.quantity,t.quantity) AS quantity
     ...
  FROM
     trades t
  LEFT
  JOIN
     trade_versions tv
    ON
     t.id = tv.trades_id
   AND
     tv.deleted IS NULL

You could even set up a trigger to mark all other versions as deleted when inserting a new version for any particular trade into the versions table.

[rblon]

Great thx for all the ideas

That should mean you can enter the data to the first tbale and use last_insert_id to get the ID value for trade_id in the second table. No need then (I think) for status column.
bazz

Not sure what you mean with first and second table, but inserting would still be 2-step process, right? As we need to get last_insert_id before we can update the tradeid? Also, something I was not clear about, but status can have other states as well like 'Cancelled', 'Expired', etc, which means it should be there in addition to a timestamp column.

The version can be determined by the timestamp.

You shouldn't need to update the old row or most recent version. Instead you should always insert a new row with the new data into versions table and account for this throughout the rest of the application code.

You can even make things a little easier on yourself and add a deleted column to the version table. The active versions deleted column will be NULL while the older versions are the TIMESTAMP for when the version was deleted. Then your joins to find the most recent version (or if one exists) are just a matter of joining on trades_id and delete IS NULL.

This seems a neat solution. Agreed that version number is unnecessary. A disadvantage seems that you need to set-up / maintain two tables. And an other issue I see so far is when you want to cancel a the trade. Clearly giving the tv.deleted column a timestamp will be unsatisfactory, as that will then return the orginal trade with your query. But perhaps that is where an additional status column can be used.

An advantage will be that tradeid's will be sequential (as opposed to my approach where there will be gaps). On the other hand a change would still involve updating the old row (as tv.deleted needs to get a timestamp). And hence, the question why this would be a better set-up (apart from the sequential tradeid's)?

[rblon]

Actually, a related question: does the auto-increment attribute mean the values of that column need to be unique?

If not, then there would be no reason to use both an id & tradeid column (a new trade would use auto-increment, while an updated version would specify the id of the orginial trade when doing the INSERT).

[rblon]

I did a simple test (with MySQL & MyISAM) and it seems you can indeed specify id column as INDEX instead as PRIMARY KEY, which allows to use auto-increment and non-uniqueness.

[IBazz]

whats the purpose of the id column?

If you have table data which as single column or a group of columns, is a unique value then that COMPOSITE KEY should be the PK and not an auto_increment id.

If I understand a bit about what you are doing, the trade_versions table, as proposed by oddz, has the trade_id as a FK. If there are different versions, I would think there will be more than one trade_id in that table so you will need some column as a differentiator between one trade and another. This may be a trade name or some other column detail but if there isn't such a thing, which, as a composite key with trade_id (fk), would be unique, then you will likely need to make one.

then your trade_versions table might be lke this

create tbale trade_versions
( trade_id int not null
, trade_name varchar(99) not null
........
, primary key (trade_id, trade_name)
, constraint trade_fk
    foreign key (trade_id)
      references trades(id) add your 'on delete' instruction here and your 'on update' instruction too
)

hth

bazz

[IBazz]

BTW; if you are joining tables and using FKs shouldn't they be Innodb and not MyIsam?

bazz

[felgall]

Ideally shouldn't you use database logging to maintain the audit trail rather than attempting to update the database design for it.

[rblon]

BTW; if you are joining tables and using FKs shouldn't they be Innodb and not MyIsam?

I think JOIN is supported by all engines; FOREIGN KEYS are indeed not supported by MyISAM. However, I only mentioned for completeness that I used MyISAM for the simple test case. I do think InnoDB doesn't require auto_increment columns to be unique either.

Anyway, I am thinking now of the following set-up:

CREATE TABLE trades (
  tradeid INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  tradedt DATETIME,
  stockid INTEGER UNSIGNED,
  price DECIMAL(10, 4),
  quantity INTEGER UNSIGNED,
  ...
  ...
  status VARCHAR(10),
  statusdt DATETIME,
  version INTEGER UNSIGNED,
  INDEX (tradeid)
)

• for a new trade, the tradeid will be auto-generated, status 'Live', and version 1.
• if the trade gets changed, the orginal record will be updated to status 'Changed' and statusdt gets a new timestamp. Then, a new record will be added with the orginal tradeid, status 'Live', and version 2.
• if the trade then gets cancelled, the status of the 'Live' trade (= latest version) will be updated to 'Cancelled' and statusdt will get a new timestamp

So, the combination of tradeid and version will be unique (but there can be several records corresponding to one tradeid).

Ideally shouldn't you use database logging to maintain the audit trail rather than attempting to update the database design for it.

I am not completely sure what database logging is. But I could imagine one would like to view the audit trail (= different versions) within the user application. Would that be possible with database logging?