Ever store calculated data?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Ever store calculated data?

  1. #1
    Mike Guest

    Ever store calculated data?


    I am working an an application that needs to store lots of data.

    Should you ever store data that has been calculated from other fields in
    its own field?

    Also is it common practice to ever code a user name directly into a table,
    even though it exists in a contact table...

    My reason for asking is this is a financial application, and a table represents
    a bucnh of electronic forms. We have to have the users name associated with
    the form and the name has to be the name of the contact at the time the form
    was filled. Thus if the persons name changes, we don't want to reflect her
    married name in the old form as it was originally filled out with her maiden
    name...

    What do you guys think?




  2. #2
    Kevin Guest

    Re: Ever store calculated data?


    Hello,

    On your first topic, I think at first thought most people would say not to
    store calculated data. This obviously depends on how easy it is to recreate
    the calculated data as needed. We have some rounding logic implemented in
    VB code that is used for calculations. Because of this, we have to store
    the calculated values. Why, is because T-SQL and Crystal can't duplicate
    the rounding logic exactly so you could get penny off errors if you attempt
    to recreate the calculated values using SQL or in reporting. For financial
    apps, this can be a big problem. Just a case where you need to store calculated
    values.

    On your second topic, I think this is where strict relational data theory
    breaks down. Maybe there are exceptions in relational data theory for what
    you might call 'historical' data. I'm not a relational data theory expert
    in the least. In practice, I have found you have to consider whether the
    original state of the data needs to be kept or whether the relational model,
    which would cause current changes to reflect on past transactions is okay.

    Kevin

    "Mike" <mike@nospam.org> wrote:
    >
    >I am working an an application that needs to store lots of data.
    >
    >Should you ever store data that has been calculated from other fields in
    >its own field?
    >
    >Also is it common practice to ever code a user name directly into a table,
    >even though it exists in a contact table...
    >
    >My reason for asking is this is a financial application, and a table represents
    >a bucnh of electronic forms. We have to have the users name associated

    with
    >the form and the name has to be the name of the contact at the time the

    form
    >was filled. Thus if the persons name changes, we don't want to reflect

    her
    >married name in the old form as it was originally filled out with her maiden
    >name...
    >
    >What do you guys think?
    >
    >
    >



  3. #3
    Mike Guest

    Re: Ever store calculated data?


    Thanks Kevin,

    I appreciate your response.

    "Kevin" <kverble@solomon.com> wrote:
    >
    >Hello,
    >
    >On your first topic, I think at first thought most people would say not

    to
    >store calculated data. This obviously depends on how easy it is to recreate
    >the calculated data as needed. We have some rounding logic implemented in
    >VB code that is used for calculations. Because of this, we have to store
    >the calculated values. Why, is because T-SQL and Crystal can't duplicate
    >the rounding logic exactly so you could get penny off errors if you attempt
    >to recreate the calculated values using SQL or in reporting. For financial
    >apps, this can be a big problem. Just a case where you need to store calculated
    >values.
    >
    >On your second topic, I think this is where strict relational data theory
    >breaks down. Maybe there are exceptions in relational data theory for what
    >you might call 'historical' data. I'm not a relational data theory expert
    >in the least. In practice, I have found you have to consider whether the
    >original state of the data needs to be kept or whether the relational model,
    >which would cause current changes to reflect on past transactions is okay.
    >
    >Kevin
    >
    >"Mike" <mike@nospam.org> wrote:
    >>
    >>I am working an an application that needs to store lots of data.
    >>
    >>Should you ever store data that has been calculated from other fields in
    >>its own field?
    >>
    >>Also is it common practice to ever code a user name directly into a table,
    >>even though it exists in a contact table...
    >>
    >>My reason for asking is this is a financial application, and a table represents
    >>a bucnh of electronic forms. We have to have the users name associated

    >with
    >>the form and the name has to be the name of the contact at the time the

    >form
    >>was filled. Thus if the persons name changes, we don't want to reflect

    >her
    >>married name in the old form as it was originally filled out with her maiden
    >>name...
    >>
    >>What do you guys think?
    >>
    >>
    >>

    >



  4. #4
    James Guest

    Re: Ever store calculated data?


    "Mike" <mike@nospam.org> wrote:
    >
    >I am working an an application that needs to store lots of data.
    >
    >Should you ever store data that has been calculated from other fields in
    >its own field?


    I would say your answer depends on the circumstances. If you forsee the
    formula/business rules used to create the calculated field changing then
    yes store the result. A (poor?) example is applying tax rate to a transaction.
    If the formula used to calculate the tax (i.e. say a new tax is introduced)
    and you did not store the results of the calculation, you'll have no way
    in the future to recreate the transaction using the original formula. This
    is not a perfect example but it gets the point across.

    Another reason to store the calculated value is speed. For instance if you
    need constant lookups or say, sums, on the calculated result, storing it
    saves recaculating the result every time.

    If none of this applies, I see no reaon to store the calculate result.

    James.

  5. #5
    Chris Hylton Guest

    Re: Ever store calculated data?


    I have to agree here...depends on the circumstances...and the points made
    here are all good ones...typically, you don't store this type of data, but
    in your case, you are going to either have to do that...or use some sort
    of a time stamp as part of your key to go after old data...which is a major
    hassle.

    Storing calculated data is pretty common, and it's not necessarily a bad
    thing...for example, many data warehousing applications store data for some
    of the very reasons mentioned...and, cubes are a perfect example of storing
    calculated data, or rollup data, in a fast format for reporting purposes...

    So...store away in my opinion...space is cheap...and in reporting cases/needs
    like yours, it's almost mandatory...

    Chris


    "Mike" <mike@nospam.org> wrote:
    >
    >I am working an an application that needs to store lots of data.
    >
    >Should you ever store data that has been calculated from other fields in
    >its own field?
    >
    >Also is it common practice to ever code a user name directly into a table,
    >even though it exists in a contact table...
    >
    >My reason for asking is this is a financial application, and a table represents
    >a bucnh of electronic forms. We have to have the users name associated

    with
    >the form and the name has to be the name of the contact at the time the

    form
    >was filled. Thus if the persons name changes, we don't want to reflect

    her
    >married name in the old form as it was originally filled out with her maiden
    >name...
    >
    >What do you guys think?
    >
    >
    >



  6. #6
    David Guest

    Re: Ever store calculated data?


    The rule should be to store data such as the results of a calculation or the
    name of a person if the values are likely to change over time.

    Examples:
    If you need to calculate an amount paid from a unit price, and the unit price
    will change over time you can either store the amount paid with the order,
    or the unit price at the time of purchase and calculate from that.

    Similarly, if you need the name of the person at the time they filled in
    the form you should store that. You might want to consider storing a link
    to the contact also, so that you can find their information under their new
    name.


    "Mike" <mike@nospam.org> wrote:
    >
    >I am working an an application that needs to store lots of data.
    >
    >Should you ever store data that has been calculated from other fields in
    >its own field?
    >
    >Also is it common practice to ever code a user name directly into a table,
    >even though it exists in a contact table...
    >
    >My reason for asking is this is a financial application, and a table represents
    >a bucnh of electronic forms. We have to have the users name associated

    with
    >the form and the name has to be the name of the contact at the time the

    form
    >was filled. Thus if the persons name changes, we don't want to reflect

    her
    >married name in the old form as it was originally filled out with her maiden
    >name...
    >
    >What do you guys think?
    >
    >
    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center