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?

Best Answer

The question you should be asking is whether product attribute changes in general are of interest or if you are particularly concerned with changes in price.

What you probably really want is to record the price actually paid in your sale transaction details. This is something that you'll need for proper financial accounting. There are lots of things that could impact the price paid. Maybe there was a sale on. Maybe the customer has a coupon or a loyalty discount card? Note how being on sale could be considered an attribute of the product, but a coupon or customer discount would never be.

Don't think of the price as being (only) an attribute of the product. Once it becomes the price actually paid, it's an attribute of the sale, not an attribute of the product. Think of the price on your product table as being "the current normal price". The price on your sale transaction detail is "the price actually paid on this sale". You will need to have both because they are semantically different. You just need to know where to look depending on which meaning is of interest for any specific purpose.

Related Question