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.