Save changes of records of products in Azure DB in way that we can see historical data

Question

I am curious about "best" model for my purpose of storage of data (include historical changes) in Azure DB that I will be able to see timeline of that changes.

Example: We have some company which making products. These products changing prices or description very often, sometimes they got deleted and next day they publish them again (with some changes).

My app checking that company every hour and check all products which they have. Save them to Azure DB (doesn't be save in the same entity as company have them, just very short version):

- ID, 
- date, 
- price, 
- description, 
- few links for pics, 
- location.

Sometimes they update price so I check it again and if I see that record from DB have different price than on company shop. Or they change description, or etc.

• I would like to store that change of. (Include previous value and new value.)
• Include when it happen (date and hour, nothing too specific).

I was thinking db scheme like:

But I can say that:

• I will have like 10 fields.
• Checking thousands products every hour.

So I feel bad that in every small change I store whole Product record again. Also it can happen that one product can be removed, and Add under different IdOfProduct (this is generated just by sequence on company side), but with the same location and description but for example different price.

So I am also trying way how to store that products (even historical one, which are deleted) that if I will have "new product" that I can make fast compare with database if there is something similar (check location, after that check description, maybe even price) and If its similar, make a record to another table that there is possibility of the connection to the old product which was deleted).

Can anyone give me advice how to Store these information that I will not waste the resources (db size or cpu resources on checking linked products from some messy db design)?

Thank you. Every hint will help me :)

Answer 1

Saving any historical data will use extra database storage and other CPU resources because every CRUD operations will be saved as a separate entity.

One way to save the historical data and changes is by using Temporal tables. Temporal tables are a programmability feature of Azure SQL Database and Azure SQL Managed Instance that allows you to track and analyze the full history of changes in your data, without the need for custom coding. Temporal tables keep data closely related to time context so that stored facts can be interpreted as valid only within the specific period. This property of temporal tables allows for efficient time-based analysis and getting insights from data evolution.

Depending on whether you are starting new development or upgrading existing application, you will either create temporal tables or modify existing ones by adding temporal attributes. Perform these actions on either SQL Server Management Studio (SSMS) or as Transact-SQL statement.

Use context menu item “New System-Versioned Table” in SSMS Object Explorer to open the query editor with a temporal table template script and then use “Specify Values for Template Parameters” (Ctrl+Shift+M) to populate the template:

You can also create temporal table by specifying the Transact-SQL statements directly, as shown in the example below.

Note: The mandatory elements of every temporal table are the PERIOD definition and the SYSTEM_VERSIONING clause with a reference to another user table that will store historical row versions:

CREATE TABLE WebsiteUserInfo
(  
    [UserID] int NOT NULL PRIMARY KEY CLUSTERED
  , [UserName] nvarchar(100) NOT NULL
  , [PagesVisited] int NOT NULL
  , [ValidFrom] datetime2 (0) GENERATED ALWAYS AS ROW START
  , [ValidTo] datetime2 (0) GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )  
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.WebsiteUserInfoHistory));

You can also extend the existing table to become temporal, as shown in the following example:

ALTER TABLE WebsiteUserInfo
ADD
    ValidFrom datetime2 (0) GENERATED ALWAYS AS ROW START HIDDEN  
        constraint DF_ValidFrom DEFAULT DATEADD(SECOND, -1, SYSUTCDATETIME())
    , ValidTo datetime2 (0)  GENERATED ALWAYS AS ROW END HIDDEN
        constraint DF_ValidTo DEFAULT '9999.12.31 23:59:59.99'
    , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

ALTER TABLE WebsiteUserInfo  
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.WebsiteUserInfoHistory));
GO

CREATE CLUSTERED COLUMNSTORE INDEX IX_WebsiteUserInfoHistory
ON dbo.WebsiteUserInfoHistory
WITH (DROP_EXISTING = ON);

To know more, please refer https://learn.microsoft.com/en-us/azure/azure-sql/temporal-tables.