Where should calculations in SQL-table be placed ?

**homerun4711** · 19th January 2011, 08:10

Hello!

I am working on a small software to manage invoices and customers.

Now I am not sure which is the correct place to do some calculations and if the results should be stored within the SQLite database.

Some examples:

1.) If I add entries to an invoice (article number, article name, price/net, VAT,...) should I also add the total price (price/net + VAT) to the database?

2.) After finishing an invoice, should I add its total value and total VAT to the record that contains the rest of the invoice data (customer, date,...)?

3.) While adding an article I just take the values of QLineEdits (Quantity, VAT, Price one item) that send textChanged()-signals to re-calculate the value of another QLineEdit that contains the value of all-items-incl.VAT.
The calculation is done by using QVariant's toString and toDouble and afterwards the values are written into the QLineEdits and if the user klicks "Add Entry" the values are written from the QLineEdits to the database (converted to REAL).

But somehow I feel that this is not the correct way to do something like this...

Do you have some ideas how this is done the right way?

Kind regards,
HomeR

**stampede** · 19th January 2011, 14:58

Do you have some ideas how this is done the right way?

1) For general, do not store results of such trivial computation (a+b) in database, save only the values needed for computing them. I don't see a point in keeping it.
2) You can save total values of VAT and price, this way you'll need to query just for one record to get total value of invoice, instead of querying for all invoice entries and add the prices. You are likely to do something like asking for a total income / VAT for a given day (or customer), keeping those values could help.
3) Maybe better would be to use a QSpinBox for quantity, this seems more natural to me. And maybe use a QLabel (or other non-editable widget) for total value + VAT, its not meant to be edited by users directly. Having all ui input items connected to a slot that refreshes the result seems ok. This may seem trivial but always validate user input before pushing data into database, this can save you some troubles.