SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question 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:

    Code:
    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.

  2. #2
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  3. #3
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,044
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    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.

    Code SQL:
    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.

  4. #4
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Great thx for all the ideas

    Quote Originally Posted by IBazz View Post
    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.

    Quote Originally Posted by oddz View Post
    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)?

  5. #5
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(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).

  6. #6
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  7. #7
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    Code:
    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

  8. #8
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    BTW; if you are joining tables and using FKs shouldn't they be Innodb and not MyIsam?

    bazz
    Last edited by IBazz; Dec 11, 2009 at 13:33. Reason: clarification

  9. #9
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,597
    Mentioned
    24 Post(s)
    Tagged
    1 Thread(s)
    Ideally shouldn't you use database logging to maintain the audit trail rather than attempting to update the database design for it.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  10. #10
    SitePoint Enthusiast
    Join Date
    Dec 2009
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by IBazz View Post
    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:

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

    Quote Originally Posted by felgall View Post
    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?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •