4 ms·

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 s

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!

kevincox 3y ago
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.
- evrimoztamur 3y ago
  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.

kevincox 3y ago

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.

evrimoztamur 3y ago
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.