i am developing an ordering system with the usual accounts, orders, transactions, products, etc but have been having some trouble with the complexities of some queries and so have now been thinking about changing the design somewhat.
currently, i am calculating the account balance from all the transactions and orders made on it but am wondering if anyone would recommend having an account balance field instead.
this would seem to be a simpler and possibly more flexible approach, but was wondering how other people design this sort of system. what are the pros and cons of both approaches? when is it a good idea to store and update fields manually and when is it good to calculate?
here are some of my tables: