Is it a bad idea to keep a subtotal field in database

Question

I have a MySQL table that represents a list of orders and a related child table that represents the shipments associated with each order (some orders have more than one shipment, but most have just one).

Each shipment has a number of costs, for example:

• ItemCost
• ShippingCost
• HandlingCost
• TaxCost

There are many places in the application where I need to get consolidated information for the order such as:

• TotalItemCost
• TotalShippingCost
• TotalHandlingCost
• TotalTaxCost
• TotalCost
• TotalPaid
• TotalProfit

All those fields are dependent on the aggregated values in the related shipment table. This information is used in other queries, reports, screens, etc., some of which have to return a result on tens of thousands of records quickly for a user.

As I see it, there are a few basic ways to go with this:

1. Use a subquery to calculate these items from the shipment table whenever they are needed. This complicates things quite a bit for all the queried that needs all or part of this information. It is also slow.

2. Create a view that exposes the subqueries as simple fields. This keeps the reports that needs them simple.

3. Add these fields in the order table. These would give me the performance I am looking for, at the expense of having to duplicate data and calculate it when I make any changes to the shipment records.

One other thing, I am using a business layer that exposes functions to get this data (for example GetOrders(filter)) and I don't need the subtotals each time (or only some of them some of the time), so generating a subquery each time (even from a view) is probably a bad idea.

Are there any best practices that anybody can point me to help me decide what the best design for this is?

Incidentally, I ended up doing #3 primarily for performance and query simplicity reasons.

Update:

Got lots of great feedback pretty quickly, thank you all. To give a bit more background, one of the places the information is shown is on the admin console where I have a potentially very long list of orders and needs to show TotalCost, TotalPaid, and TotalProfit for each.

Answer 1

Theres absolutely nothing wrong with doing rollups of your statistical data and storing it to enhance application performance. Just keep in mind that you will probably need to create a set of triggers or jobs to keep the rollups in sync with your source data.