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