Post reply

Table Design for Tracking Changes To A Record

  • July 3, 2017 at 6:11 am

    #322345

    For the sake of this example assume we have an Employee table:
    First Name
    Middle Name
    Last Name
    Salary

    I need the ability to query this data such that I can get the effective state of the data at any point in time.
    If I enter a date into a query I need to be able to see what the data looked like on that day (historical perspective).

    I'm thinking of having a Employee table with the aforementioned fields and an EmployeeHistory table with two additional fields: UpdatedDate, UpdatedUserId.
    Every time a record is inserted or updated the changes are made to the Employee table and copy of the current version of the record is placed in the EmployeeHistory table.
    (I could use a trigger OR I could simply put additional insert statements into the insert/updated statement of the Employee table.  Recommendations?)

    With that, if I wanted to see the state of the record on a given data I could query the EmployeeHistory table and retrieve records as they existed on a given day while keeping the Employee table with just the current status of the records.

    This is how I've been doing things but I thought I'd run it by some database pros to see if there is a better way to handle this problem.

  • July 3, 2017 at 8:04 am

    #1949147

    What version of SQL Server? This is built in to SQL Server 2016. As for design, I'd look at Temporal Tables and how they are set up. If you are on a previous version, use the same system.

    Learning About Temporal Tables

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply