The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

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.

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.

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.

Originally Posted by oddz

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)?

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).

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.

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.

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).

Originally Posted by felgall

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?