none
Using audit trail as time machine? RRS feed

  • Question

  • I would like to do

    SELECT * TABLE t (using data from 1st of march 2012)
    

    I already have a nice audit trail of all tables in the database. It basically makes a copy of all rows that change, storing all columns of the changed row in an hstore column. This means that we have the data available, but I am unsure of whether it is the best way to store the data for a database time machine.

    To give you more context, we are creating accounting software. This means that we need to be able to recreate all reports which we have offered to the customer (they can see them on our website and get updated continuously).

    What sorts of problems do you anticipate that I will run into with this approach? Is there a better approach?

    Some facts

    • Each row in the main table gets edited 5 times on average.
    • The main table has 32 columns (could get reduced to only 7 that need to have an audit trail)
    • We will only ever have 1 million users on our software, each will have ~700 rows in the main table.
    Saturday, January 17, 2015 3:38 AM

Answers

All replies

  • Here is my favorite approach:

    • Each table has a corresponding history table
    • Write stored procedures (or triggers) to make sure that all actions are logged to the history tables
    • On insert, add a row to the history table with start = now() and end = 31.12.2999
    • On update, first update the most recent history record to end = now(). Then insert a new row with start = now() and end = 31.12.2999
    • On delete, update the most recent history record to end = now().

    Now you can write a point-in-time query, even with joins:

    select g.groupname, p.productname, p.price 
    from products_hist p, product_groups_hist g 
    where p.id = g.id 
    and p.start <= now() and now() < p.end
    and g.start <= now() and now() < g.end
    
    • Marked as answer by Tekken Tekken Saturday, January 17, 2015 1:33 PM
    • Unmarked as answer by Shanky_621MVP Wednesday, January 21, 2015 2:07 PM
    Saturday, January 17, 2015 9:24 AM
  • Moderators,

    Please look closely, I have already pointed it and you would find that ALL answers which Takken go is from Engsoochean. This is highly not possible its clear favoritism. There are couple of users who are marking ONLY HIS ANSWER and ONLY VOTING HIS POST helpful.

    I am not saying answer is incorrect but question is not relevant to Databse design.

    I have ONLY UNMARKED the answer as I want other moderators to have a look and then they can mark the answer.

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    Wednesday, January 21, 2015 2:09 PM
  • >On insert, add a row to the history table with start = now() and end = 31.12.2999

    now() is from an app language. Is this an app question or database?




    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014




    Friday, February 6, 2015 4:45 PM
    Moderator
  • Moderators,

    Please look closely, I have already pointed it and you would find that ALL answers which Takken go is from Engsoochean. This is highly not possible its clear favoritism. There are couple of users who are marking ONLY HIS ANSWER and ONLY VOTING HIS POST helpful.

    I am not saying answer is incorrect but question is not relevant to Databse design.

    I have ONLY UNMARKED the answer as I want other moderators to have a look and then they can mark the answer.

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article

    MVP

    You are right !

    This is obviously the same user, that cheat for points, as it seems. Moreover, he have other users as well!! It look like he opened several users for cheating, and they all creating new questions and marking the main user which is EngSoonCheah

    For example those are the same users that he opened to get points (they mark only his responses):
    https://social.msdn.microsoft.com/Profile/pandorahacker/activity
    https://social.msdn.microsoft.com/Profile/tekken%20tekken/activity

    Did you post this to Ed / fissues ?

    It might be good solution to clear all EngSoonCheah's points! unless he have a very good explanation for this. 

    This user should be banned, or better solution is to clear all his points, and let him start from scratch (even if he have some points which he got for real helping). For this we need Ed :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]


    Saturday, February 7, 2015 7:30 PM
    Moderator
  • > You are right !

    I detected 3 posts with the same dubious pattern. There were moved erroneously to Database Design.




    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014





    Sunday, February 8, 2015 6:05 PM
    Moderator
  • Here is my favorite approach:

    • Each table has a corresponding history table
    • Write stored procedures (or triggers) to make sure that all actions are logged to the history tables
    • On insert, add a row to the history table with start = now() and end = 31.12.2999
    • On update, first update the most recent history record to end = now(). Then insert a new row with start = now() and end = 31.12.2999
    • On delete, update the most recent history record to end = now().

    Now you can write a point-in-time query, even with joins:

    select g.groupname, p.productname, p.price 
    from products_hist p, product_groups_hist g 
    where p.id = g.id 
    and p.start <= now() and now() < p.end
    and g.start <= now() and now() < g.end
    

    EngSoonCheah, can you explain why you seem to be answering the same people who mark your answers and vote your posts as helpful?

    Thanks!


    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Tuesday, February 10, 2015 4:46 AM
    Owner