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?

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?
>
>
>

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?
>>
>>
>>
>

"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.

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?
>
>
>

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?
>
>
>