September 14, 2005 at 8:09 am
Is it proper database design to store calculated fields in a table.
Thank You
September 14, 2005 at 8:22 am
Depends on the situation and how you store it. What is your need at this point?
September 14, 2005 at 8:32 am
I will be calculating a couple of monetary sales goals based on a predefined percentage of previous years sales for each customer.
Could you explain when it would be appropriate and when it would not be and why.
Your input is greatly appreciated.
September 14, 2005 at 8:50 am
IMHO, Initially I would say no that would not be a great place to use a calculated field. But again I guess it depends. Do you store the previous years sales in a field on the customer table?
if no, and this value has to be calculated, then I would stick to my original answer. If yes, it would be okay to have a calculated column. I always try to minimized the overhead a calcuated column would have, if its an inline calculation thats fine, outside of that, it is alot of pain to the db.
But say you have a Birthdate field, and you want a calculated filed for Age, that would be appropriate.
my $.02
September 14, 2005 at 8:52 am
My rule of thumb is this.
If the field is calculated, accessed often but with a value that never/rarely changes, you can keep it in the db. The best exemple is the amount of a bill. We could recalculate it everytime but we don't need to as the amount won't change unless we change the order. If the field is accessed only once a month for reporting, then you can calculate on the fly (or keep it in a calculated column so that the server can do it for you when needed). The last case is when the data changes often, then just recalculate when you select/present the data (like for stock exchange).
This is jsut a very global generalisation that will need to be changed on a case by case basis, but that'll get you started.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply