I’m creating an invoice and inventory database for my first time. For the invoice database I have a customer, employee, invoice, invoice details, and products table. Relationship schema is set up and everything seems to be working. Where I’m confused is how to get the total from an invoice to update an invoice total column in the invoice table. Thank you kindly for any help.
I have a similar invoicing system I built and I am wondering why you want to update this table at all as you suggest?
I think it better to generate this total when needed via queries on the fly. This way if an underlying data table changes (like an invoice details line item is added), then any totals are automatically updated when needed.
Here’s the potential rub: If you do it the way you suggest, then there are really two totals to consider, one static (that you pre-computed and saved) and one dynamic (created on the fly when needed) and these two can be at odds with each other if the underlying data is edited. And we’re all humans, and data errors do need to be fixed from time to time.
If you save a static total, then any edits must faithfully be followed by total re-generation, otherwise your database is inconsistent.
I think it’s better to just use the dynamic total method via queries when you need the total, and not save a static total.
Hope this makes sense. I have tried it your way over the years, and, well, I don’t do it that way anymore.
Thank you! Yes, this makes sense.
What I forgot to add was why I tried going this route. I have employees who will be getting 5% commission from the invoices they are associated with, so I wanted to keep track of the invoice totals. The way you suggest makes more sense, and will also help with keeping track of their commission totals, which I can keep in a spreadsheet.
Thanks again!
@SpaceCadet Please use a Comment for this type of response. Answers should be used to respond to original question.
Also, you don’t really need spreadsheet. Should be able to do with query & simple report for Commissions.