MySQL: How to keep history of an item?

Question

I have a process wherein I need to keep the history of a database records information, however the user needs to be able to change it at any time they please.

Scenario:

1. Seller creates an item with price of $5 and name of "foo"
2. Buyers buys item, an order is created linking to that item id
3. A while later, seller updates item name to "foobar" and item price to $6
4. Buyer views order history. The item name should be "foo" and price should be $5 since that's what they bought it at, but they are "foobar" and $6, respectively

This happens because when the seller updates the item, they are updating the same item the order is related to.

I thought of 3 possible solutions to this problem, and I would like to get your thoughts on which one you think is best (maybe from your prior experience), or a better solution I have not yet thought of. This is my first time dealing with this situation, so not sure how best to proceed without needing a refactor later.

My solutions:

1. Make the item name and price immutable.
   • Bad UX, cause now user has to delete item and recreate it if they want to make a modification
   • Requires some kind of deleted_at column in case user wants to delete the item after it has been purchased so that I can still keep it for referencing later to grab history data
2. Create a second table for history purposes
   • Not horrible, but requires a second table with a different name, not a big fan of the idea
   • Would have to run queries potentially twice to check both tables for similar data, as opposed to just querying one table
3. Create two records in the same table, and mark a boolean flag or some other flag to differentiate from historical/current records
   • I like this one the best, but not sure if the boolean flag may have any negative performance implications

Answer 1

I've encountered this issue too, particularly in product catalogs where the price changes frequently. Or the price may be on sale or discounted for a specific customer for some reason.

The only solution I've found is to copy the relevant product details to the customer's order record at the time they buy the product. In your example, at least the product name and the product price would be copied.

This might seem like it goes against the philosophy of "don't store redundant data" but it's not redundant—it's a fact that the customer bought the product for some specific price on a specific date, and that is still a useful fact forever, even if the current price for that product changes.

There should still be a link to the original product table, so managers can track how many orders included each product, for example. But the current price in the product table does not affect the record of each customer's order.

You might also need to create a product history table, to keep a record of all the times the price or name was changed. But that's for historical record-keeping only, it wouldn't affect typical queries during shopping or buying activities.

In this design:

• Product table always stores the current price.
• When a customer buys a product, they copy the current price into their own order record.
• When a manager changes a price, the app creates a new record in the ProductHistory table.
• The most recent record for each product in the ProductHistory table matches the current price for the same product.