Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

Database history table

Options
  • 26-09-2011 8:54am
    #1
    Tom Dunne
    Registered Users Posts: 23,212 ✭✭✭✭


    Myself and a colleague are disagreeing over the design of a history table in a database.

    Briefly, if we have a primary key ITEM_ID, we want to record all events that happen to that ITEM_ID in a history table with a timestamp.

    So, we could have:

    ITEM_ID, TIMESTAMP, STATUS (other columns omitted for clarity)

    with ITEM_ID and TIMESTAMP being the composite primary key, allowing data such as:

    701, 26/09/11 10:00, Rejected
    701, 26/09/11 10:05, Approved
    701, 26/09/11 10:07, Rejected
    792, 26/09/11 10:00, Rejected
    792, 26/09/11 10:06, Approved

    and so on.

    Where we are disagreeing is with the composite primary key. It is a very low-volume database, a document approval system, so it is extremely unlikely that two approvals will happen at the exact same time, therefore ensuring the composite key will be unique.

    Is this the way to do it, or can anyone suggest a better way?


Comments

  • Options
    #2
    stevenmu
    Moderators, Society & Culture Moderators Posts: 9,689 Mod ✭✭✭✭stevenmu


    I'd suggest that each row have it's own unique ID (HISTORY_ID or something like that), which can act as the primary key for the table. You can then add a composite index for the ITEM_ID and TIMESTAMP columns (and/or seperate indexes for each column).

    This guarantees uniqueness for the primary key, and gives you fast/easy searching by ITEM_ID and/or TIMESTAMP. It will also make it much easier for your application to refer to, and pass around, a single history row. You may not need to now, but could easily in the future and it would be much harder to add in later.

    The overhead of the extra column would be negligble IMO, and while it may not be 100% correct from a theoretical normalisation point of view, it's much better from a practical one.


  • Options
    #3
    oeb
    Registered Users Posts: 2,793 ✭✭✭oeb


    What I would do in a situation like this is give the table it's own separate primary key (Just an auto incrementing int) and index the item id field (Which is where all the searching is likely to take place).

    While you are correct that collisions are unlikely to happen with the composite id, it can still happen, and the overhead of having the extra field and index would (in my opinion) be less than that required to ensure collisions don't happen (programatically).


  • Options
    #4
    Tom Dunne
    Registered Users Posts: 23,212 ✭✭✭✭Tom Dunne


    stevenmu wrote: »
    The overhead of the extra column would be negligble IMO, and while it may not be 100% correct from a theoretical normalisation point of view, it's much better from a practical one.

    To be honest, I am not too worried about the overhead (it's not going to be a very big database), it's the second part I'd be more concerned with.

    However, as you say, it does appear more practical.

    Looks like my buddy was right. :mad::)


Advertisement