Database design: where to place the total amount fields

Question

I am designing an app that tracks orders. Each order can have > 1 workItem, and each workItem can have a separate price.

I was storing the prices of the workItem in the workItem table, thinking that in the UI, or for reports, gathering the job cost (both billed to the customer and paid to the contractor) would be calculated with a query of existing data in the workItems table.

Does it make sense to leave it this way or store the total amount in the Order table? If I go with the latter, isn't the data redundant? Maybe there are performance considerations of such a move. What do you think?

Answer 1

It depends on how your database is being used.

The ideal solution would be to keep the separate items in your work item rows. That way you avoid duplication of data. For example, when you add or update a work item you would otherwise have to update both the work item and the total.

With appropriate indexes a query like this is typically performant:

SELECT i.*, SUM(wi.amount) total
FROM invoice i
JOIN workitem wi ON i.invoice_id = wi.invoice_id
GROUP BY i.invoice_id

That being said, if you find a performance problem with this you might denormalize your data model and store a total as well. But only go down that route if you need to. In my opinion, it shouldn't be done preemptively.

Answer 2

As you say, performance and usage are the key to the correct descision here. And what is the correct descision now may not be in the future.

A requirement may be to list all orders just displaying the total value. If you don't store that value, you'd have to aggregate the order items for each order just to get the totals. If you stored this value alongside the order table then you wouldn't need to include order items in the table.

You could extend this thinking to the number of order items too. That value could be calculated, but if it was needed in the overview, big peformance gains may be gained just be storing it alongside the orders table.

Answer 3

I would not store the total in your database unless performance becomes an issue.

Rather calculate it on the fly, as needed for reports or for display.

Answer 4

If you follow normalisation rules, you'll omit calculated values, such as Totals and generate them on the fly.

However, there sometimes occasions when you might choose to de-normalise a little and store such values explicitly.

In terms of storage space, it's generally not an issue on most platforms these days, so storing the extra data is not a problem. And sometimes you can improve performance or simplify your code, when de-normalising in this way. Equally, you decision can also effect maintainability, or perhaps increase complexity.

In your example, if you store the total against the order, every time you amend an item you are hitting the workitem table and then you have to update the order table as well. There seems to be little benefit to this, so I wouldn't go this route... but as I said, there are occasions where you might sensibly choose to store data, rather than derive it on the fly.