none
Database design: History for all Entries: Extra history table or same table with composite primary key (timestamp + id) RRS feed

  • Question

  • Hello everybody,

    I was asked to design a history functionality for my database, which means that every record should be stored with a timestamp, comment (created, edited, deleted) and a user.

    In my eyes there are 2 possibilities:

    1)  Store all entries in the same table, and add created and deleted column. Then I could use an insert- trigger to mark the current row with deleted is null.

    Pros:

     - no extra tables needed

    Cons:

    - very huge tables

    - have to deal with history relevant aspects (affects keys have to add where deleted == null to every query). For the querys I Could use views only to get the relevant data but the axtra columns for the design remain.

    2) Use an extra Table

    Pros:

    - clean tables

    - Performance: I don’t have to query for the current entry (although deleted == null should be fast), only when the user wants to see the history

    Cons:

    - Many tables: As my database has to deal with many languages there is always a details_table to each original table to store language relevant data. So for example I have Person and Person_Details and I would need Person_History, Person_Details_History

    - Maintenance:  Any changes has to be made in the original and the History table

    - Redundant data: Anytime when the user adds an entry I would copy the entry to the history table and add the history relevant information

    I am not so happy with both solutions. I started with approach 1) but had to realize that my database is too complex. I already had composite keys or keys consisting even of 3 columns and a lot of relations. So due to complexity I can only work with extra tables.
    I counted 19 details – tables, but I guess I have no other chance than creating 19 history tables for that.

    Any comments or hints to speed up the history table creation process are highly welcome.
    Tuesday, February 3, 2009 11:47 AM

Answers

  • Separate tables for auditing/history is a much better and cleaner option in my opinion.
    I don't think storage should be a concern at all. And yes SQL Server Management studio will let you generate scripts for database objects.

    I used this option in one of solutions but I also deleted the foreign keys from the history tables.

    • Marked as answer by wuz Thursday, February 5, 2009 7:55 PM
    Thursday, February 5, 2009 3:33 PM

All replies

  • Hi,

    So many different ways to do this - I guess performance is going to depend on how reasonably the existing tables are normalised?  If they're not, or they're not efficiently indexed or whatever then performance isn't going to be an issue, as this isn't going to run well at all!

    You can write a shadow set of tables if you wanted, same structure and run a script overnight to horizontally partition them?

    You could write instead of triggers to stop physical deletions, and instead mark as deleted, combined with the above.

    Really depends on how the history is intended to work - will it be used to search often?  What performance will it need?

    Martin.

    MCSD, MCTS. Please mark my post as helpful if you find the information good!
    Wednesday, February 4, 2009 11:14 AM
  • Thx, viewing the History won't be needed that often, so one additional point for extra tables. Well my db schema is quite complex and i already have composite pks from time to time i decided for extra tables (option 2). I only wanted to avoid the manual generation of all the entity_details_history tables but than i recall that i am working with the mighty ms sql server. So i can write an t-sql script which generates me the tables.

    I am absolutely new to t-sql but it should be possible to do something line this.
    Wednesday, February 4, 2009 2:57 PM
  • Separate tables for auditing/history is a much better and cleaner option in my opinion.
    I don't think storage should be a concern at all. And yes SQL Server Management studio will let you generate scripts for database objects.

    I used this option in one of solutions but I also deleted the foreign keys from the history tables.

    • Marked as answer by wuz Thursday, February 5, 2009 7:55 PM
    Thursday, February 5, 2009 3:33 PM