Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2017
    Posts
    1

    Unanswered: Keeping Track Of History Of Tables

    This is an interesting topic for me as I am learning more about SQL Server. I'm working for a small business that has all their work in excel and now transferring them into a real application with a backend.
    I've read many articles on this topic and a lot of them are back in 2008 and not sure what is the proper way to do keep track of changes to the tables. My easy solution that I thought of right now is have one table that keeps track of all changes to my tables. It would store: ID, table name, column name, prev value, current value, date.
    The only thing I can see wrong with this is it would only be good for UPDATE. It wouldn't do well with INSERT/DELETE(can't imagine ever using DELETE in my app but good to have).
    I've thought of shadow tables of each table but that seems like I'm storing a lot more data and duplication. What is the best practice of keeping track of history today?(I would like to avoid audit plugins if possible) I'm shooting for performance but it willing to try other methods. We will be using one machine. Not sure if it is a good idea to store the history in 2 databases in the same instance or just 1 database(would like to know more about this with performance if it makes a difference, or have them on 2 different instances). Thanks!

    Edit: DELETE THREAD, SORRY WRONG SECTION!
    Last edited by freefora11; 08-24-17 at 18:17. Reason: wrong section

  2. #2
    Join Date
    Oct 2007
    Posts
    162
    Provided Answers: 9
    I've always liked keeping history in tact within the current structure, such as an end date for the current row. You can store insert and update userid columns if you want to see who did what. This doesn't resolve for deletes though. In this manner, the only column being updated is your end date and then a new row is inserted to the table with the updated data.

Posting Permissions

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