There is a table of goods ( Goods ). Each product has a price. When creating an order ( Order ) goods are attached to it using the additional Goods__Order table. At the same time, after ordering, the prices of goods in the order should not change, even if they have changed in the Goods table.

One option is to save the price in the Goods__Order table. But the number of fields that should not change may become more, and therefore I don’t want to transfer them all to this table + the option is not very universal.

The second option, as it seems to me, you can log changes in the Goods table to the Goods_audit table, which will contain changes to the fields + version number (revision number). It is available through Hibernate Envers. But then in the Goods_Order table you will need to Goods_Order product id + version number + I did not find examples of what they are doing.

Which option would be better? Given the huge number of online stores, probably already have a more or less well-known solution for this. Yes, and in the options for php no Hibernate Envers.

  • one
    It would be better if you first study the accounting for centuries. And do everything as it says. It is better to hardly succeed, but almost certainly worse. The price and value of goods in the order is an integral part of the order. At least one of them should be in the order without fail. - Sergey
  • one
    There should be a version table (your second example) where the current price for the date (period) you need is stored. When you create an order, you select the data based on the date of the order. The easiest way to organize through a stored procedure - JVic

2 answers 2

  • An order is not an object of accounting, it is an object of logistic processes and customer relations. Unambiguous recommendations for logistics and CRM does not exist, the correct formulation of such processes is the organization's know-how.
  • While the order is not paid, it is essentially an offer to the client and the object of any revaluation.
  • The store may have rules for calculating the price of an individual product for a customer, taking into account discounts (sometimes even surcharges) - a discount on a promotional code, a discount for the volume of the order (total for all product items of the order), a personal discount for the customer (for example, for an order history, or a discount for individual categories of clients), etc.
  • During the time when the order is waiting for payment, a campaign to stimulate demand can start - a significant discount starts to act on certain products / groups, which cancels other discounts (promotional code, personal discounts, etc.).
  • After the order is paid (especially if it is completed), all current conditions - the base price, discounts, delivery terms, etc. - must be fixed and not subject to further changes.
  • In developed eComm Magento-type platforms, current prices are maintained in separate objects — price lists, price recalculation rules can be configured (for example, depending on currency quotations for imported goods), price history is changed.
  • Rules for recalculating unpaid orders can also be customized.

So it should be, and whether it is implemented in a particular eComm and how it depends on the platform. If you write the platform yourself, consult the sales department :-)

    1. Clumsy option In the Order table write the price at the time of order add a price field.

    2. Create a price_history table [product id, revision number, price, date, etc. if necessary] and make each price change there. In the Goods table, only the price revision id is stored.

    • Both options I wrote in the question. The question is which one is better and more expandable. That is, I am interested in pros and cons. - Victor Khovanskiy
    • @VictorKhovanskiy As far as I understand, small and medium-sized online stores do not have a history of prices. What can I say, and large so often do. Ie. 1-option. I can say that to tack on a large market is difficult to keep up with the price changes of others at the current moment when there is a large market, not that it is also to log and analyze past ones. - FORTRAN