SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Evangelist hessodreamy's Avatar
    Join Date
    Apr 2005
    Location
    uk
    Posts
    524
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Whether to save calculated values in table, or just source values

    I'm just wondering whether or not it's advisable to store in the database values which are calculated from other values in the table. This specific instance involves tax calculations.

    eg I'm storing price and tax rate, what I've been doing is also storing the tax amount (eg unit_price, quantity, tax_rate, tax_amount). Do other people take this approach? I seem to remember from uni being taught not to store calculated columns. However there are different ways to calculate tax, and different points to apply rounding (eg 3 units @ 11.53 x 20% tax could be round(11.53*0.2,2)*3 = 6.93 or round((11.53*3) * 0.2,2) = 6.92), so it may be advisable to have a concrete record of each line of tax charged (for audit purposes, perhaps). I'm also storing the product's net amount (eg 11.53 * 2) and gross (11.53 * 2 * 0.2), and these are probably overkill but sometimes it saves me a couple of seconds by not having to calculate these columns. Pretty much pure laziness, also at the time I was trying to emulate the layout of the sage accounts package (though I don't know what data is actually stored at their DB layer).

    Another issue is storing a product_total column in the orders table, to represent the total charge for products in that order. Easily calculated with a join, but storing this calculated field saves a join.

    So how far to go? raw data only, or liberal use of calculated columns? Perhaps calcualted and aggregated columns should go in a different table?

  2. #2
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I think you should look at this slightly differently. I have encountered similar issues in the past especially when it comes to finance data. If the taxable amount is fixed at 20% then this is something that you could in theory calculate each time. But what happens if the tax rates goes up to 21%? Should your data still be taxed on 20% or at the new level of 21%? If old data should be taxed at 20% then I would store the calculated amounts to avoid any confusion.

    If on the other hand the values can always be calculated based on the base table, then have a look at creating a view which does the calculations for you against the base table.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by hessodreamy View Post
    However there are different ways to calculate tax... so it may be advisable to have a concrete record of each line of tax charged (for audit purposes, perhaps).
    answered your own question right there


    Quote Originally Posted by hessodreamy View Post
    Another issue is storing a product_total column in the orders table, to represent the total charge for products in that order. Easily calculated with a join, but storing this calculated field saves a join.
    i would do that too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,169
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    The rule of thumb I have always used, is if you want the data displayed back to the user exactly how it was shown the first time, it is likely best to store those values somewhere.

    Yes, you can use tax tables, product price tables, etc (in case the tax changes, or the product price changes) and associate an id that must be joined to each order, but usually it is easier and far less complicated to just create a order invoice table that stores the values you need to display.

    I've never really decided if the better technique is to store an OrderInvoice object in the database (one that is serialized from your code, so you can deserialize it back), or to have a column for each property. I don't know that it matters much one way over another, as there are advantages and disadvantages to each.


Bookmarks

Posting Permissions

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