Ask Your Question
1

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

asked 2013-02-25 18:07:21 +0200

Libre Lyrae gravatar image

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!

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-10-26 12:17:27.030800

1 Answer

Sort by » oldest newest most voted
0

answered 2013-03-07 07:57:30 +0200

qubit gravatar image

Hi @Libre Lyrae,

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
    
edit flag offensive delete link more

Comments

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!

Libre Lyrae gravatar imageLibre Lyrae ( 2013-03-07 16:07:02 +0200 )edit

@Libre Lyrae -- you're quite welcome. Good luck!

qubit gravatar imagequbit ( 2013-03-07 21:03:19 +0200 )edit

Question Tools

Stats

Asked: 2013-02-25 18:07:21 +0200

Seen: 396 times

Last updated: Mar 07 '13