How should I persist order data over time without tying it to underlying product pricing data that is subject to change?

Question

I have an application that processes and stores orders. Each order is composed of line items that have various things like pricing, sizing, colors, etc. My question is how do I persist that data over time without tying it to underlying data that is subject to change.

For example, someone orders something which is composed of 5 things that cost $1 each, for a total of $5. However, at some point in the future the costs for the individual things changes to $0.50. Now when I bring up the old order it's total cost is $2.50 instead of $5.

Initially, I thought of just not allowing the children tables to be updated, however that is not really feasible as the pricing is based on huge pricing grids that the customer only wants to update and not create new ones every time a price changes in the slightest. Each pricing grid take hours to set up and the pricing changes every couple of days.

Another thought is to serialize the entire order and work from that. However, that makes it hard to update the system and change anything. Old orders will no longer display properly and might even crash the system.

This is a pretty general problem and I'm looking for a best practices viewpoint.

Answer 1

You need to save the values in each order and they should not be connected to the price grid, but copied from it.

Answer 2

Dani is correct. For auditing purposes, you create records with values that do not change over time instead of linking to values that might change over time.

So your line items should have a copy of what was purchased and describe that. So if you have product information you need to keep, save it in the lineitem record with prices, fees and totals.

Answer 3

NEVER base your order totals on the current price of the product. A typical data structure for online catalogs, etc. is to have an Order table and a Line Item table. The order table contains all the pertinent info for orders (order total, userid, date/time, etc.), while the line item table holds the product id, the item's price at the time it was placed in the cart, as well as tax info, etc. and the order id.

Basically a one-to-many relationship between the order table and the line items. Line items become a representation of the product's price, etc. at the time of the order.