Quick question regarding calculated fields. Lets take an invoice for example.
I have an invoice table and an invoice_detail table. The detail table holds all the line items for the invoice. I currently store the invoice_total inside the invoice table, but I’m wondering if I should be doing the same for each line item inside the invoice_detail table.
In the invoice_detail table, each row has a rate and quantity fields, which are used to generate the total.
My question is, would it be best practice to store the “total” as another field in this invoice_detail table? So rate, quantity, total? or simply calculate them on the fly?
It seems like for reporting, when you want to get a lot of records, having the totals already generated would be a time saver.
My personal approach is - do it the way that is most likely to be reliable, if this is not the fastest method, optimise only once you see performance that is too slow to meet your needs… optimising too early is a recipe for trouble. It costs time and effort and might not give you any real benefit.
Calculating on the fly will be more reliable. Storing pre calculated fields is essentially duplicated data, hence you will have to make sure it gets updated every time invoice_detail changes. You might end up with data that is no longer correct!
Having said that, if you design your app properly all DB reads/writes to the invoice tables will probably be hidden in a single model class. As long as that model interface is done right, you can change the way data is stored in DB at any stage.
For me it depends on the number of records that need to be aggregated. For things like invoices, which typically have no more than 20 lines or so I wouldn’t store the totals but calculate everything “on the fly”.
If you’re talking about a poll or something like that however, I would definitely store counts in the tables when needed.
I guess my rule of thumb is: don’t introduce redundancy (and possibly insert/update anomalities if you’re not careful!) UNLESS your application is really taking a performance hit.