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:
- Create a
product snapshottable 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
- Add a
valid until(or maybe just simple
active) field in the table. Set
valid untilvalue to null if it is still valid. When creating a new record update
valid untilfrom 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
- 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