What are some of the best practices for tracking updates to a record over time in sql?

Question

I have a product table with a primary key @productid (bigint), a product number (int), and a version (int)

Any time someone makes changes to the product record ONLY, I plan on inserting a new row in the database with the same product number and version number + 1. This will provide me with the historical tracking I need for the record because I can see the version changes throughout time.

/* Selecting the current version is simple */
Select top 1 *
from products
where productnumber = @productnumber
order by version desc  

However, my problem comes in with the Foreign key one-to-many or many-to-many relationship tables. This table points to many others (i.e. product pricing with date ranges, product categories, etc.) which also need to be tracked.

/* Product categories, pricing */ 
 /* Should I use @productnumber here? How do I track changes to these records? */

select name
from productcategories
where productid = @productid

select price
from productpricing
 where productid = @productid and
       StartDate > @StartDate and
       EndDate <@Enddate

So now any time there is a version change, I plan to to re-insert new category and pricing records with the new Primary Key product id that was generated..This is going to lead to a ton of duplicates, especially if no changes were made to these records.

Also the issue comes in with - what happens if a category is removed but there were no changes to the product record? I would want to see who removed the category. Essentially, a full audit is needed on each table.

I have seen some different examples but most of them only seem to deal with a record in one table and not a record that is a part of one-to-many or many-to-many relationships. I was hoping this could be done without the need of additional tables.

Are there any better methods or practices? Is this going to be a performance nightmare?

Answer 1

If you are using a newer version of SQL Server as you are, you can should look into temporal tables as this might be your best option.

If you need to support older versions, my preferred method is to have a history table with a new PK column, change flag (I,U,D), a date modified, user that made the change, and all of the columns from the primary table. I then index the column related to the PK of the non-history table. Triggers don't impact performance too much if you don't put logic in them. Example (pseudocode):

Table: Car
Column: CarID INT IDENTITY(1,1) Primary Key
Column: Name varchar

Table: Car_hist
Column: Car_histID INT IDENTITY(1,1) Primary Key
Column: Change char(1)
Column: DateOfChange DateTime2
Column: ChangedByUser (varchar or int)
Column: CarID <-add a unique non-clustered index
Column: Name varchar

You can write a generator in SQL that generates the script to create the history table, indexes, etc. It helps if you have a consistent table design practice.

Now the reason: I rarely have to query history tables, but when I do, it is almost always for a single record to see what happened and who changed it. This method allows you to select from the history on the parent table's PK value quickly and read it as a historical change log easily (who changed what and when). I don't see how you can do that in your design. If you are really slick, you can find or write a grid that diffs rows for you and you can quickly see what changed.