0

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?

3

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.

1
  • Agree with @joel-brown - the order system will need to capture the price the customer actually paid for each line item. Also, it's common practice to include the SKU name and other supporting information. Essentially everything needed in order to reproduce a copy of the customer receipt/order confirmation should be stored in the order and order_lines tables. If not, you will need to store a hard copy (PDF) of the receipt for accounting purposes. – matiasf Nov 7 '20 at 21:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.