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
versioncolumn 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_historytable, with the columns of the
producttable plus additional
change_timecolumns 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
versioncolumn to the products which is incremented every update. When there is a purchase, copy the product to a
purchase_producttable. 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?