...to prevent messing up with data updates, such as prices, titles, of the products that are placed in an order.

Namely, a customer buys 3 items: for $5, $10 and $33 and pays for them. All is well. When I as an owner of a shop step in and, before I've delivered those products to a customer, decide to descrease the price of the product, say, #2. Its new price will be $8 instead of $10. And I'll also rename the product #3 a little bit. A customer then would go to a status tracking page and they'll see updated data. Yes, they've paid and the order is being delivered, but the data has already changed in the database. They'll be consufed.

And so will be I in a year after I'll have changed the prices again, renamed something, etc... and decide to view history of the orders for a year.

Question 1: is there a practise of making a snaphot of the current prices, names, total order price and other characteristics of the products that constitute an order, as well as other details (chosen shipping rate and shipping method, taxes, discounts, etc) at the moment when it's being placed? Rather than calculating those dynamically when an "order page" is opened?

If yes, does it have a name? And are there recommendations of how to do it properly?

Question 2: where and how should I store a snaphot?

option #1:

I'd have to create multiple tables then:

  • frozen_products
  • frozen_discounts
  • frozen_shipping_method_and_rates


that will have the same structure as their dynamics corresponding ones.

Laborious. Is there a better way?

option #2:

along with an order, in the "orders" table. But how again, given the fact that an order is a single row? For instance, the products in an order is a list. How would I store a list with its characteristics (price, weight, colour, material, what have you) in a row in such a way that'll be more or less easy to retrieve in the future? Not as a string.

1 Answer 1


The best way of freezing the information for this would be to have a separate table for invoice details. This would store not the basic invoice information (date_of_purchase, customer_ID) , but it would store the individual items in the invoice (Invoice_ID, Product_ID, Product_QTY, Product_Price_Per_Unit). Check out this link here, it covers the general idea that I'm talking about. https://itextpdf.com/en/resources/books/zugferd-future-invoicing/3-simple-invoice-database

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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