For example if I have an order that has many line_items. The total =
(sum of line items) + shipping_cost. Would it be wrong to just add a
"total" field in the orders table? That basically serves as a cache.
When an order is created or modified it keeps that value up to date. It
makes writing queries easier that use total and displaying the total is
less computational. What do you think? Thanks for your help.
I think it's fine. As long as you're careful to make sure this gets updated whenver line_items gets updated.
And that you are aware of the race condition and when it's really important (ie. when you charge the users credit card) that you get the *real* value.
I do this all the time for other things like rolling up "views per day" into a "total views" column.