What is the best way to store a list of items in an order?

Question

Database

Hello.

What is the best way to store data about the items included in the order, if it is expected that the purchased items will change over time, but the data of the items in the order must remain unchanged?

Example:
Placed Order1, which contains Product1 with a cost of 100 rubles.
The Order1 page displays Item1 with a cost of 100 rubles.
The administrator changed the cost of Item1 by 200 rubles.
On the Order1 page, Product1 should be displayed with a cost of 100 rubles, not 200.

The first thing that comes to mind is to store a full or partial copy of the purchased product at the time of placing the order, so the data for displaying the product will be taken from the copy and changes in the main product will not affect the composition of the order. However, this solution has an obvious disadvantage.

How do you solve such problems?

Answer 1

The first thing that comes to mind is to store a full or partial copy of the purchased product at the time of placing the order.

Another option is to use Event Sourcing, but it has the same problems, plus the fact that you need to redo the entire application for it. Or just keep a log of changes and get a cut at the right time - Event Sourcing at the minimum.
There is no other option - the old data must be stored in order to use it in the future. You can store only some basic fields: price, name, etc. to reduce the volume. But you need to understand that the more data you store, the more room for maneuver you have in the future - display some additional information, calculate statistics, etc.
I usually either start a separate table for the goods of placed orders, in which data is duplicated, or in the basket I start a json column that stores the slice at the time of checkout. Logging is also carried out, but it is inconvenient to use. And it's more logical to "calculate" the value once and save it forever than to pull it from the logs every time (and still store it in the cache anyway).
In the case of the json column, we first stored everything in general - the model and those associated with it - with a large margin. And about six months later, when they began to run into the size of the data, they analyzed the cases and left only the necessary fields.

Answer 2

1. Keep a history of product changes
2. Before buying, check if the current copy of the product has gone to the archive
3. Warn users if something has changed
4. The entire order is paid for the full amount, so it doesn’t matter if the cost has changed since the start of the payment single item
Profit