Say I run a store that sells products of some sort. A product has typical attributes, most importantly price. When a customer makes a purchase, an invoice which references one or more products is generated.
Question: If the staff of the store updates the product, we may not be able to figure out how much a customer paid for a particular item because that product’s price has been updated. How do I handle this use case? Three options I can think of:
- Add a
version column to the products table. When making a change, insert a new row and increment the version number, instead of updating. When I reference a product from an purchase, I reference a specific version of the product so I always know how much a product cost at the time of purchase.
The problem I see with this is that, ignoring performance and ever-increasing need for storage, whenever I need to update a price (or other product attribute), I need to update all foreign key references to that item. In my database I may have a dozen such tables, most of which do not need ‘historical’ information like past prices.
Create a separate
product_history table, with the columns of the
product table plus additional
change_time columns with all previous versions. When updating the product table, I will save the previous version to the history table. Purchases, and any other table that needs historical information will reference rows in
version column to the products which is incremented every update. When there is a purchase, copy the product to a
purchase_product table. The purchases table will reference
product. I would only keep one row per version of a product, so there is no worry of the table exploding in size when a lot of people buy the same item.
3 seems slightly better 2 because I only keep versions of products that I actually need (not for every single change of the product price). However, that means I won’t be able to run analytics on when a product was NOT selling because (say) the price was too high…
Or, is there a more obvious solution?