Is it possible to lock a record's values in Libreoffice base

[LibreLyrae]

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!

[qubit1]

Hi @LibreLyrae,

The price that belongs on the invoice is the price that was current when the order was processed. So you need to record this information somehow. I see at least three methods:

1. You pull the data out of the db and into a static file (like a printable invoice).

The invoices aren’t in the database anymore, but the prices are never affected by future db updates.

2. You keep a historical log of prices of items, and use that to determine an invoice’s total based on when the invoice was filed.

If you know the price of an item at any point in time, you can combine that information with the list of items on the invoice and the date of the invoice to re-create the invoice.

This method is a bit complicated, and has some nuances that could easily make things go haywire and cause problems for invoices (which is probably not the best idea when writing new code to deal with $$).

The “locking” you describe above could possibly be implemented with this kind of mechanism behind the scenes.

3. Throw the invoice information per item into a separate table

Seems the simplest to me.

For every invoice, you have an invoice table

    id  |  purchased_by | etc... |
    ----+---------------+--------+
      1 | Foobar        |        |
      2 | Qubit         |        |

You also have your items table

    id |   item   | current_price
    ---+----------+--------------
     1 | LO hat   |        10.00
     2 | LO shirt |        20.00
     3 | LO mug   |         5.00

Then you have your items_in_invoice table

    invoice_id | item_id | number |  price  | discounts |  total
    -----------+---------+--------+---------+-----------+--------
             1 |       2 |      4 |   20.00 |           |   80.00
             1 |       3 |      1 |    5.00 |           |    5.00
             2 |       1 |    900 | 9000.00 |    500.00 | 8500.00

[LibreLyrae]

Thank you qubit for the thorough answer, it seems to me like the invoice information table would be the best fit for this project and this client. THANKS!

[qubit1]

@LibreLyrae – you’re quite welcome. Good luck!