I am designing an invoice database for a client using Base. It has an Invoices, Orders, OrderItems, and Items tables. The Items tables contains the product price as well as other Item-specific information. The OrderItems contains quantity, tax rates (set to default values), Orders contains discounts, shipping, etc,
My problem with this schema is that if I was to change the price of an Item in the Items table then it will change it on all the invoices including historical ones, which would then make the totals wrong and show them as having credits or balance dues. Is it possible to ‘lock’ a record’s values in LibreOffice base so that updates to the other tables will not change the values of the historical records?
If this is not possible my next approach would be to add an ItemPrice table.
Note: I did not make the pricing at the Order level as I wanted it to fill in the amount automatically from the items table rather than having to manually enter the price each time. They key for this particular client is to keep things simple and automated on his end, even if it makes more work on my end.
Any suggestions greatly appreciated, Thanks!