When placing an order in a shop, should a snapshop be taken of the products in an order also?

Question

...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

  etc...

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.

Answer 1

Orders/Receipts

Are a separate data structure to the product catalogue/shopping cart.

The catalogue/shopping cart dynamically update. If the price goes up, so does the price of the item in the cart.

When you go to checkout, the shopping cart is turned into an Order. At this point the prices are discounted and fixed, units are pre-allocated from stock, a reservation timeout is started, and the customer is provided with payment information, and the ability to cancel the order.

When the customer makes payment, a receipt is generated (or the order is marked as paid) and the fulfilment process commences.

Data Structures

The way of doing this is to have Orders completely separate from the Shopping Cart/Product Catalogue.

On the plus side this creates two bounded contexts over smaller domains. On the minus side this makes it harder to reissue orders, and share code.

Generally this solution require two tables, an order table, and an order line table.

In the order table place all of the overall details: Total Due, Total Paid, delivery details, customer information, order level discount, etc..

In the order line table store each of the specifically ordered products/services: the product/service details, reference numbers, how many units, any item level discount, price per unit, line total, etc...

You can of course pull out repeating data into supporting tables, but that leads us in the direction of the next solution.

---

Alternately

You can have a single set of data-structures. A Product Catalogue and an Order.

The Product Catalogue will need to be versioned. When a product is updated the process is to insert a new row, and update a column in the old row to point to the new version of the product. A New product is just inserted, and a no longer offered product needs to be marked as such.

This allows an Order being constructed in a Shopping Cart to have its products updated to the latest version, or at least off of no longer offered versions. Once the customer moves to check the exact products are validated (updated as necessary) and then locked in forever more. Their price won't change, nor will any other details. This also allows customers to reissue the order (updating to the latest version of each product).

The order still has two tables (order and order line).

---

Auditing

The main thing you need to keep your eye on is Audibility. At some point in time expect that a government body, the share holders, a market offer, etc... is going to require a third-party audit.

When that Audit happens they are going to want to verify exactly what was charged, how much, and all of the other details. If you keep these fields as automatically generated, then the auditors are also going to demand change history and code level reviews to ensure that they did not change in the past.

To the end, regardless of specific approach, you should record all of the data given to a customer on their order/payment receipts as it was calculated on that day. This way if there was a bug in the software, you can find it, but also your software is consistent, even after the bug has been fixed.

That paper trail is gold to an auditor, and will make your life so much easier. Try explaining why the calculation will always come to the same result. Particularly when there is a code commit that actually does change the result (by intention or not). Compare that to explaining that this field contains the value on the order, no further calculation required.

Answer 2

Yes, you are correct in needing a "snapshot" of the Product details at the time the sale is made. This technique as it related to SQL databases is often referred to as "denormalization".

Basically, for whatever database element you have that represents a product of X quantity on order number Z, you'll copy the price/name fields into the row from the Product Catalog row at the time the sale is generated. That way, you will know for an older sale, what the product name/price were at the time of the sale, no matter what they are right now.

An alternative some use instead of this is that the Product Catalog doesn't hold one price per item, but rather holds unchangeable rows representing the price/name at a specific datetime. So when they "change" the core product price, a new row with a 'starting' datetime of now gets inserted. Now, your Orders table doesn't need to be denormalized, it'll just have to know how to inspect the Product Catalog table to locate the name/price for that product on the same datetime as the order. Both approaches are valid although the denormalizing one is probably a bit simpler to implement.