1

I have a database with an invoices table and invoice_items table. For simplicity, let's just say theinvoices table has the following structure:

| id | name | created_at |

The invoice_items has this structure:

| id | invoice_id | tital | created_at |

Pretty simple, a parent invoice table and the line items go into invoice_items with a relationship setup.

What I'm wondering is, how effective it is to has a sum column on the invoices table? Would it be bad to have all invoice_items per invoice added up on the fly to show the invoice total amount? Or would it be better to have a SUM column which is static, and is pulled automatically when invoices are listed - then updated specifically whenever an invoice changes?

Just trying to weigh up the pro's and con's - because whenever an invoice changes, the SUM column needs to be updated whereas if it was just always calculated based on the line items dynamically, would that be a drastic design flaw or be acceptable?

1 Answer 1

1

As ever - it depends.

If you have a blisteringly quick DBMS or only a handful of items in each invoice, then totalling them up on the fly would be the cleanest (and, therefore, "best") option.

The "Ivory-Tower Database Purists" would say that you should never store anything that you can derive dynamically (citing Date-of-Birth vs Age). Realistically, it depends on just how long it takes to do that derivation!

If you have an "ordinary" database, or hundreds or even thousands of items in each invoice and you need that summarised total frequently, then maintaining the summary field at the Invoice level might be the better solution. [Per-row] Triggers are the way to achieve this, so that no update to any item gets missed.

All that said, if I were in your position, I'd go with on-the-fly totalling ... at least until the application's performance began to suffer, and then I'd think about "optimising" this aspect of it.

1
  • Fantastic, thank you. That means my approach is correct. Looking at many (if not most) similar situations on older software, there is always a SUM column in the invoices table, but I'll keep on with the on-the-fly totalling for now.
    – BFM2K
    Feb 27, 2020 at 17:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.