0

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: enter image description here

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).

enter image description here

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 :)

4
  • 1
    10 columns is not a problem. Your timestamps indicate checking the products every 15 minutes. You can create a separate link table that links row 4 and row 5 as similar. Your database will grow rather large, so charge the customer appropriately. Sep 3, 2021 at 11:37
  • I mean 10 columns doesnt see like problem (its not some production business with 50 fields). But still thinking, i will have new records just when something changes (so if product is the same, there will not be changes). But when is change there - I am making fully copy of that product. And how to say what exactly changed (because now i check it and I know what changed, should I save that information over another table and enum "priceChanged" or should I lost this information and next time when I will see two records of one product just compare it again?
    – user3132499
    Sep 4, 2021 at 9:42
  • ALso, if I will have few users which want to get notification what product change, but they dont want to get notification all of them on the same topic. For example: User1 want to get notification of changes for products in his region, User2 want to get notification on products on his region + product category type = Audio. How to store that information? When I will check server, parse products, compare them if they is changes. And if there is changes save them to DB as new records as you mentioned. But if I will have also table with Users and their "filter" on what they want to get notif.?
    – user3132499
    Sep 4, 2021 at 17:18
  • 1
    I can't anticipate the requirements that you haven't described in your question. This is not a discussion forum. Hire a good data analyst and give him all the requirements. Sep 4, 2021 at 17:57

1 Answer 1

0

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:

enter image description here

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.