4 ms·

Sure, you can do that. But if you do some changes in your data model or logic you need to be extremely careful that all existing invoices still show exactly the

by andix 3y ago

Sure, you can do that. But if you do some changes in your data model or logic you need to be extremely careful that all existing invoices still show exactly the same data. You probably need to use immutable price calculation logic for that too. Keep the old logic after an update and only use the new logic for new invoices. Because over the years there will be braking changes in your price calculation logic. A lot of effort for not so much added value. The systems I worked on put the calculated price of every item on every invoice into the database. So it can’t “drift“ over time. Off course they still keep the reference to the article too. It’s also much easier/faster to calculate sums and statistics one one table column instead of doing many joins every time you need the price.

hot_gril 3y ago
Maybe I'm not thinking of a complicated enough case, but I don't see why there'd be so many breaking changes. Say you add a new table for some special kind of promo, that doesn't affect the old receipts. At least this wasn't an issue in the last few e-commerce sites I dealt with. In any case, I would stay far away from having to preserve multiple versions of logic. It's also fine to denormalize a bit by copying the final price somewhere for performance, convenience, or sanity checking, but that's the kind of thing you bolt on after. If you're managing account balances, you actually need this for concurrency control alone.
- andix 3y ago
  I think you can't compare e-commerce to POS. Another example: A customer can stay a few hours inside a restaurant. Their tab stays open during that time, items are added at different times, and in the end the tab is closed and the invoice created. Prices of the items could change during that time (somebody noticed a wrong price during the shift and fixed it). There could be a happy hour with lower prices. Let's say Version 1.0 uses the prices that were valid when the invoice was finalized. Version 2.0 implements a happy hour feature, and now prices need to be calculated based on the time the item was added to the invoice. Without the right backwards compatible logic that may change the previous invoices. Or let's say you have two branches with one POS system. It's a small business, so they don't have a redundant internet connection, and a system that can work offline. One branch goes offline for a day, they receive the price updates a day late. They may know about that, but off course they don't close for the day, they just keep operating with the old prices. Their system will sync back the created invoices once connectivity is established again. The manager notices it and calls the branch to update the price for the caviar, because it recently tripled and that's a really important change. So they change the price of that item now in the disconnected database, at a different time than in the main database. On the next day the branch reconnects again and you need to sync that. Sounds like fun, doesn't it? :D It's much easier to just copy the new invoices to the main database as they are and replace the price list with the version from the main database. Let's imagine an airline, with a lot of cash registers on the plane for food and drinks. Some of them don't connect for maybe a week on a regular basis. There are perfectly immutable solutions for all those cases for sure. But they may become really complex really quick.
  - 8note 3y ago
    Isn't it the easiest to copy them in with the price that was charged? I don't imagine it's fun to find out that the price charged did not match the price list, after you've already forgotten how much you charged
    - andix 3y ago
      The systems I contributed to did exactly that. They stored the resolved price and other metadata to the invoice tables. To store the exact prices, article names and so on that the cashier saw on his screen and the customer on the printed invoice. Even if the price calculation was wrong in the first place, that is the data that was available at the moment of the sale and that is what the customers paid. Correcting the invoice is a manual step anyway, the customer needs to come back, dispute the invoice and ask for a refund. If the customer was charged less that's usually just bad luck for the business. "Fixing" the bug in the database/software doesn't get them the missing money. Another thing are prices from external systems. For example if a hotel puts parking charges on the invoice. They may park the cars in a garage of another company and on check out they validate the ticket with the system of the external company and just put the calculated price on the invoice. "Parking - 43.21$". There may be no price calculation for parking in the hotels POS system at all, so no relation to a price table can be referenced.
  - hot_gril 3y ago
    If you mean just recording the product without the actual price used, that loses information and isn't what I'd do. I would write the price IDs that were used and input different prices during happy hour. Same complexity as writing the $ amounts except I'm writing fkeys instead so I track which pricing structure was used. If the customer uses a coupon, I relate to it. If I have a distributed system with often-disconnected followers, I sync in new prices and coupons without affecting the old ones. If local managers are allowed to temporarily change prices, I preserve the local price when syncing in the headquarters' price (in fact the local one would probably be in a separate "price overrides" table).
lmm 3y ago
> A lot of effort for not so much added value. It's not a lot of effort; in fact I'd argue it's free, or even negative cost. If you're in the habit of following that paradigm then it's no harder than the mutate-everything-in-place paradigm. > It’s also much easier/faster to calculate sums and statistics one one table column instead of doing many joins every time you need the price. WTF? Did 1970 call and ask for their hardware back?
- hot_gril 3y ago
  > WTF? Did 1970 call and ask for their hardware back? The joins can actually get slow enough to matter for a UI. Depending on your use case, denormalized $ values can make total sense, but that should be separate from your properly normalized tables.
- andix 3y ago
  I've seen POS databases with millions of invoice positions. There is a substantial delay if you do 10 joins on a few million records. Calculating a sum over one column of millions of rows is substantially faster. It could be 2 seconds vs 200 seconds.

by andix 3y ago

hot_gril 3y ago

Maybe I'm not thinking of a complicated enough case, but I don't see why there'd be so many breaking changes. Say you add a new table for some special kind of promo, that doesn't affect the old receipts. At least this wasn't an issue in the last few e-commerce sites I dealt with. In any case, I would stay far away from having to preserve multiple versions of logic. It's also fine to denormalize a bit by copying the final price somewhere for performance, convenience, or sanity checking, but that's the kind of thing you bolt on after. If you're managing account balances, you actually need this for concurrency control alone.

andix 3y ago
I think you can't compare e-commerce to POS. Another example: A customer can stay a few hours inside a restaurant. Their tab stays open during that time, items are added at different times, and in the end the tab is closed and the invoice created. Prices of the items could change during that time (somebody noticed a wrong price during the shift and fixed it). There could be a happy hour with lower prices. Let's say Version 1.0 uses the prices that were valid when the invoice was finalized. Version 2.0 implements a happy hour feature, and now prices need to be calculated based on the time the item was added to the invoice. Without the right backwards compatible logic that may change the previous invoices. Or let's say you have two branches with one POS system. It's a small business, so they don't have a redundant internet connection, and a system that can work offline. One branch goes offline for a day, they receive the price updates a day late. They may know about that, but off course they don't close for the day, they just keep operating with the old prices. Their system will sync back the created invoices once connectivity is established again. The manager notices it and calls the branch to update the price for the caviar, because it recently tripled and that's a really important change. So they change the price of that item now in the disconnected database, at a different time than in the main database. On the next day the branch reconnects again and you need to sync that. Sounds like fun, doesn't it? :D It's much easier to just copy the new invoices to the main database as they are and replace the price list with the version from the main database. Let's imagine an airline, with a lot of cash registers on the plane for food and drinks. Some of them don't connect for maybe a week on a regular basis. There are perfectly immutable solutions for all those cases for sure. But they may become really complex really quick.
- 8note 3y ago
  Isn't it the easiest to copy them in with the price that was charged? I don't imagine it's fun to find out that the price charged did not match the price list, after you've already forgotten how much you charged
  - andix 3y ago
    The systems I contributed to did exactly that. They stored the resolved price and other metadata to the invoice tables. To store the exact prices, article names and so on that the cashier saw on his screen and the customer on the printed invoice. Even if the price calculation was wrong in the first place, that is the data that was available at the moment of the sale and that is what the customers paid. Correcting the invoice is a manual step anyway, the customer needs to come back, dispute the invoice and ask for a refund. If the customer was charged less that's usually just bad luck for the business. "Fixing" the bug in the database/software doesn't get them the missing money. Another thing are prices from external systems. For example if a hotel puts parking charges on the invoice. They may park the cars in a garage of another company and on check out they validate the ticket with the system of the external company and just put the calculated price on the invoice. "Parking - 43.21$". There may be no price calculation for parking in the hotels POS system at all, so no relation to a price table can be referenced.
- hot_gril 3y ago
  If you mean just recording the product without the actual price used, that loses information and isn't what I'd do. I would write the price IDs that were used and input different prices during happy hour. Same complexity as writing the $ amounts except I'm writing fkeys instead so I track which pricing structure was used. If the customer uses a coupon, I relate to it. If I have a distributed system with often-disconnected followers, I sync in new prices and coupons without affecting the old ones. If local managers are allowed to temporarily change prices, I preserve the local price when syncing in the headquarters' price (in fact the local one would probably be in a separate "price overrides" table).

lmm 3y ago

> A lot of effort for not so much added value. It's not a lot of effort; in fact I'd argue it's free, or even negative cost. If you're in the habit of following that paradigm then it's no harder than the mutate-everything-in-place paradigm. > It’s also much easier/faster to calculate sums and statistics one one table column instead of doing many joins every time you need the price. WTF? Did 1970 call and ask for their hardware back?

hot_gril 3y ago
> WTF? Did 1970 call and ask for their hardware back? The joins can actually get slow enough to matter for a UI. Depending on your use case, denormalized $ values can make total sense, but that should be separate from your properly normalized tables.
andix 3y ago
I've seen POS databases with millions of invoice positions. There is a substantial delay if you do 10 joins on a few million records. Calculating a sum over one column of millions of rows is substantially faster. It could be 2 seconds vs 200 seconds.