Hacker News Reader

Comment by tolien | Hacker News Reader

> For example, if a pie costs $16.95 right now and you have 1,000 orders that happened in the past at that price and then bump your pie to $17.95 you can’t go back and adjust all of those previous orders to have $17.95.

> Just about everything about that receipt should be denormalized, AKA. the details about the receipt are all self contained in that 1 row.

The way I'd probably model this would be to mark the $16.95 pie as deleted/deprecated/no longer for sale and create a duplicate entry with the price changed to $17.95, rather than creating copies of all of the product details each time I record a sale.

View on HN

> The way I'd probably model this would be to mark the $16.95 pie as deleted/deprecated/no longer for sale...

That technically works, but it plays hell with reporting and stockkeeping. The latter of which isn't a problem for pizza, of course, but can be significant in other sectors. (Nobody wants to have to keep track of a stock of "$16.95 widgets" separate from a stock of identical "$17.95 widgets".)

But there's enough other data in a receipt/invoice which needs to not change after the invoice is created, like tax rates, that it really is best to make the whole thing self-contained and immutable. Being able to confidently make statements like "yes, this is exactly the same invoice that we presented to the customer 3 years ago, and nothing about it has changed" can be incredibly important as well.

How its handled in the system I work in is that a copy the product data is made to a new object. That new object gets converted to a final invoice eventually.

A product is copied to a quote (or cart) item. A cart item is copied to an order item. A receipt or invoice is generated from an order and its items. The price of products and all their attributes can be changed without the SKU or identifier changing, but the quote, order, and invoice item is an immutable artifact.

The key problem with this system is order modifications. In order to modify an order you cancel and recreate the order from scratch. We get around this usually by piping the orders to a dedicated order management system and when orders are finalized/auth'd captured/shipped there is one final order modification that comes back to the system to capture the final state and we don't update orders continuously. This way customers see their final order state, but we don't have to cancel/recreate every change to the order along the way.

Just as the receipts are immutable, you should preferably have your products be immutable too. From the user's point of view, the foreign keys are a part of a separate layer: They will only see the products by a shortcode/name. In turn, having a modification on a product (or any other object) 'archiving' the existing version and creating a new copy can be made seamless while avoiding normalisation.

Is it a better way to do it than an audit log for changes? Maybe!

That works great until you want to see how many pizzas you sold of each size last year then you realize that you presented wrong data to the CEO because small pizzas changed price in January and large pizzas changed price in July. Your reporting only included the "current" products so the counts were all off.

The ideal schema would probably have some sort of "product" and "product revision". Orders would reference a particular revision and menu/reporting would use the product (joining in the revision).

But in this case since there are likely legal requirements to have exact copies of receipts it may also be smart to just archive a "fully rendered" copy as well to ensure that a code cleanup doesn't change rounding and accidentally modify the data on old orders.

I get your point but I am not quite sure about that example. If you have the receipts linked to a certain version of a product (via an unique ID) in a give receipt, then you can easily derive these figures via a separate item code. A large pizza could be PIZZA_L and have revision IDs 1, 2, 3... I assume that's what you mean by product/product revision, you don't need a separate table for it per se (as any number of fields for a given model may need to be changed, which makes the classification of what field belongs where much harder than having one model and a shared identifier).

You can always prepare a bad report, I don't think that this schema or that schema will solve all problems, but having a schema which keeps a complete history of changes to its data should be better than one that just lets you run an UPDATE query to actually mess the price history up.

Traditional point of sale systems generally have the notion of a “transaction log” or “tlog” which is a sequence of events within the transaction including items sold and their price at the time.

You could also use a temporal table.

Reply on news.ycombinator.com