What's the best practice for storing a record snapshot?

Use case example: Assume a user bought a product in an online store. It is a pleasant experience that the user can view a copy of what he saw when he bought the product (description, the price, etc). Surely after the product been bought it may changed or may deleted. So a direct link / view to the product page is not in the cards.

I think of some some solution:

  1. Create a product snapshot table with denormalized field included. I think this solution is the most simple. The drawback is, maybe it has the most storage requirements? But the storage price is considered affordable right now, isn't it? Kindly comment if you might have different thoughts. This might be similar to this https://stackoverflow.com/questions/720856/database-design-for-point-in-time-snapshot-of-data
  2. Add a created date and valid until (or maybe just simple active) field in the table. Set valid until value to null if it is still valid. When creating a new record update valid until from the previous valid record is updated to current date. But managing the relation of previous related object or such can be involving many operations and prone to errors? This might be similar to this thread Versioning data in a database
  3. Use temporal database implementation: https://en.wikipedia.org/wiki/Temporal_database, There are products that implement this, is it too sophisticated for this use case? or maybe something like Change Data Capture in Microsoft SQLServer? https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-server?view=sql-server-2017. The drawback maybe it takes time configuring this implementation and maybe it is just too much for this use case?

Thanks for checking out this question

  • Do you want to actually do this, or is it purely theoretical/study? – Philᵀᴹ Jun 28 '18 at 8:07
  • Yes, I actually want to implement this later. It is still in the concept though so I really want to know what's the best practice about this kind of use case. What are your thought about it @Philᵀᴹ? – otong Jun 28 '18 at 8:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.