I am building an order management system for an online store and would like to store information about the Product being ordered.

Now, if I use a Foreign Key relationship to the Product, when someone changes the price, brand, supplier etc. of the Product or deletes it, the Order will be affected as well. I want the order management system to be able to display the state of the Product when it was ordered even if it is altered or deleted from the database afterwards.

I have thought about it long and hard and have come up with ideas such as storing a JSON string representation of the object; creating a duplicate Product whose foreign key I then use for the Order etc. However, I was wondering if there is a best practice (with good performance) or what other people use to handle this kind of situation in commercial software?

PS: I also have other slightly more complex situations, for instance, I would like the data for a User object attached to the Order to change as the User changes but then never get deleted. An answer to the above question would definitely give me a good starting point.

CodePudding user response：

This price-change problem is commonly handled in RDBMS (SQL) commerce applications by doing two things.

1. inserting rows into an order_detail table when an order is placed. Each row of that table contains the particulars of the item as sold: item_id, item_count, unit_price, total_price, unit_weight, total_weight, tax_status, and so forth. So, the app captures what actually was sold, and at what price. A later price change doesn't mess up sales records. You really have to do this.

2. a price table containing item_id, price, start_time, end_time. You retrieve the current price something like this:

   SELECT item.item, price.price
     FROM item
     JOIN price ON item.item = price.item
               AND price.start_date <= NOW()
               AND (price.end_date > NOW() OR price.end_date IS NULL)
   

   This approach allows you to keep track of historical prices, and also to set up future price changes. But you still copy the price into the order_detail table.

The point is: once you've accepted an order, its details cannot change in the future. You copy the actual customer data (name, shipping address, etc) into a separate order table from your current customer table when you accept the order, and (as mentioned above) the details of each item into an order_detail table.

Your auditors will hate you if you don't do this. Ask me how I know that sometime.

CodePudding user response：

While it is hard answering your question without seeing your models.py at least, I will suggest archiving the results. You can add a boolean field called historical which defaults to False. When an order is made you need to set the previous order's (or orders') historical value to True in your view set or function. Here, historical=True means the record is being archived. You can filter on this historical column to display what you want when. Sorry this is just a high-level outline.