1. I love your chinese eyes :*
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jan 2006
    Her heart... she claims!
    Rep Power

    DB Design: storing calculated values VS always calculate them


    I am working on an ERP. in the financial section, I have to deal with calculated values e.g

    1- total of an ORDER
    2- tax on the ORDER AMOUNT
    3- any discount coupon value used while making the ORDER

    ---------- ROUGH DB DESIGN -----------------------------
    • orderID,
    • taxSchemeID
    • totalOrderAmount

    • orderID
    • productID
    • unitPrice
    • quantity
    • totalPrice(qty x unitPrice)

    • taxRate
    • schemeName


    currently, i just store the TOTAL ORDER AMOUNT when the ORDER is made, in the ORDER TABLE.
    Products sold in this ORDER are saved in a SALES table which have ORDER ID as Foreign Key.

    I have a TAX table that have tax scheme name and its RATE

    there are occasions where I just need the total amount of ORDER so I do not have to calculate anything
    but I just fetch the value from that total amount column in the ORDER table, e.g showing the customer's statement
    or Reports etc

    there are events, like editing DRAFT or VIEWING ORDER DETAILS where I calculate the tax rate and with tax
    total on the fly by joining tables.

    is this approach good for an efficient DB design?
    should I also store WITH TAX TOTAL in the ORDERS table?
    is there any other better way to handle the calculated values?

    thank you
  2. #2
  3. Headless Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Washington, USA
    Rep Power
    In general you shouldn't store calculated values because if the computation on the underlying data conflicts with the calculated result then you have a bit of a problem. Which I guarantee will happen at some point. That doesn't apply to actual "computed" (as SQL Server calls it) columns, which are basically like views anyways.

    However there are some practical exceptions (in my opinion):
    1. If the calculation is too complex and therefore expensive to do. Say, calculating the great circle distance between two points - which is more than just squares and square roots (and even that I might consider "too complex").
    2. If the calculation would be awkward to do in a query. Such as ORDERS.totalOrderAmount: getting that would require grouping and in T-SQL that could easily necessitate a subquery or two.
    3. If you frequently use the value in queries and the underlying data isn't likely to change (if ever). Such as SALES.totalPrice: once the order is complete the quantity and price won't change.
    4. If the underlying data isn't supposed to change, but if it does then it shouldn't retroactively affect the calculation. Such as a discount code's amount: you really shouldn't change that once it's been set, opting to create new coupon codes and optionally invalidate the old coupons instead, but if one does change then it shouldn't affect the total on orders placed before that happened.
    5. Probably others. It's a matter of common sense.

    Does that answer your questions?
  4. #3
  5. I love your chinese eyes :*
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Jan 2006
    Her heart... she claims!
    Rep Power
    it means I am going in the right direction. I just stored the totalOrderAmount in orders table. otherwise all calculations are done on the run time using php and joining tables.

    I avoid doing them in QUERY because they might need some logical decision making from PHP Code. My application structure is sound enough to handle variable values
    e.g tax rate. if i have to change the tax rate i can simply define a new tax scheme from the admin panel. while taking orders, user can select the tax scheme accordingly
    and its rate will be applied to the values automatically through out the application as ORDER table stores taxSchemeID for each order.

    similarly, coupons can be active/inactive and managed from the admin panel. so the change to variable values is going to be handled well.

    thank you, you have been so helpful in all these years

IMN logo majestic logo threadwatch logo seochat tools logo