# Database design for product purchase history

database-design

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:

1. 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.

1. Create a separate product_history table, with the columns of the product table plus additional version and 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 product_history, not product.

2. Add a 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 purchase_product, not 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?