I have a table (policy) representing an insurance policy. It can have zero or more fees associated with it so these are in another table. Likewise there can be various coverages each with a fee (premium). The sum total of all fees and premiums, taxes, add-ons, etc, is the policy total. Incidentally the sum of the premiums is called the pure premium.
My question from a design standpoint is should I have a policy_total field in the policy table, or just a pure_premium, Fee_Total, Add_On_Total, Tax_Total and calculate the total when needed, or no totals at all and query them when needed?
I will always need to display at least the policy_total whenever I display the policy, so if no totals are in the policy table I will need to query the sum of all the various child tables.
This leads me to believe I should go with the pure, fee, tax, add_on totals in the policy table, then the total is just the sum of these fields. This however means I need to update them in the policy table when a child table changes, but this will happen automatically in the class representing a policy anyway.
In a related example with an invoice and line items I would include a total field in the invoice table I believe.
It's a trade-off between slower reads or writes, and correct is what will serve your business best. Optimal performance may not even be necessary if response times on sum queries are good enough.
If you decide to include total columns, consider using a trigger to maintain the value rather than leaving it to the application. It often happens that tables are updated from different pieces of code, and at some point you'll want to manually manipulate the data as well.