hello forum,

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:

Account
-----------
id (pk)
name
accountStatus
merchant (fk)


AccountGroup
----------------
account (fk)
customer(fk)

Transaction
--------------
id (pk)
account (fk)
amount
customer (fk)
transactionType


Order
------------
id (pk)
account (fk)
discount
merchant (fk)
orderStatus


OrderItem
--------------
id (pk)
order (fk)
product (fk)
unitPrice (stored from product)
quantity


Product
----------
id (pk)
unitPrice
unitCost
inventory
...

so, if i wanted to calculate the account balance based on my current design, that would require several queries (i'm using mysql version 3 so i can't do sub-queries).

thanks in advance,
cam

"when is it a good idea to store and update fields manually and when is it good to calculate?"

this is based entirely on performance history

storing data always introduces the possibility of integrity problems

a payment comes in and gets into the transaction table, but the account balance update query dies for some reason

therefore you need transaction locking, rollback, etc.

so you pay a price with increased complexity, and a heckuva lot more code, and in return you get back some cycles when looking up the balance

your call