电子商务数据库设计，保存产品信息而不影响历史数据

Question

What would be the best, if not, the practical way to design an e-commerce database wherein the product information of the product purchased by a customer should not update when the product information was updated?

To give a better understanding, here's a scenario:

1. Merchant created "Product A" where it is priced at $50.
2. Customer saw Product A and purchased it.
3. Customer visited the transaction history and viewed his recent purchase: Product A priced at $50
4. Month's after, Merchant updated the price of Product A to $80.
5. Customer viewed again his transaction history. His transaction with Product A should retain at $50 and not the updated price of $80 because it was the price he paid that time.

One solution I am looking into is to save the whole product information in a table as a PHP serialized data in "purchases.product_information".

Is it even a good idea to store PHP serialized data in a column? How's performance if a user wanted to search for a text in the product information like price, item name, etc.?

Any other workaround for this?

Thank you

Answer 1

It is a case of slowly changing dimensions (https://en.wikipedia.org/wiki/Slowly_changing_dimension)

A simple solution will be to have a separate table purchase_items and have all the columns that may change over time against each item (Example:price, discount etc) and purchase and purchase_items table will have one_to_many relationship

Although, there are more complex situations where the price of the product changes on the fly based on the time at which an order is made in a day etc. In these cases, the price may not be stored against the product itself

This answer is addresses exactly the issue and you might find this helpful https://dba.stackexchange.com/questions/57992/ecommerce-orders-table-save-prices-or-use-an-audit-history-table