Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Posts
    1

    Question calculate or store?

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •