Thread: Store Calculated Values (Updating Fields)

One of the biggest "No-No's" in database development is the storing of calculations in a field. For Example:
Field1 = 2
Field2 = 8
Field3 = 10 (The sum of Field1 and Field2)

The reason why the calculation should not be stored is simple. What happens if Field1 and Field2 need to be increased by 13.6% and 8.3% respectively?

The values would now be:
Field1 = 2.272
Field2 = 8.664
Field3 = 10 (Notice how they don't match now)

Sure you can change Field3 to equal 10.936...... but what if you had 10,000 records, with 150 calculated values stored per record? Yeah, I guess you could write a script that automatically does this, but what if the script was written wrong? Or what if you have to change the values daily, or hourly? Are you going to write a script, or run a script everytime? Keep in mind 10,000 records could take quite some time to update, especially if you have like 150 fields per record to update.

One more thing..... what if the database expanded..... now field3 must equal field1 + field2 + field4 + field5 + field6, and there were 10,000 records?

The calculation should be done at the time you want to display the value. You do this in a Query or Form using an expression, or an unbound textbox whose value is an expression.

In a query, it would be:
Expr1: [Field1]+[Field2]

In a form, in an unbound textbox, the control source under the data tab in properties of the textbox would be:
=[Field1]+[Field2]

The data needed for the calculation should be stored, not the results.....

The sample below shows both ways, and how the storing of data in a field can ruin your data. It also gives a step by step on how to configure an unbound textbox to show the calculation.

I just want to add that there are exceptions to every rule. When it comes down to performance of a system, sometimes you have to store a calculated value in your table structure.

Take this forum, for instance. Thread, post, and view counts (among a few others) are all pre-calculated and stored in the table. Why? Take into consideration a forum that has thousands of posts and hundreds to thousands of visitors a day. It would be pretty demanding on the database, be it Access, MySQL, MS SQL or any other, to have to calculate these values every time a user browses a page. Just be sure you adequately prepare for miscalculations. This forum has a few functions that can be run to repair these counters so that they are accurate.

I know this could be argued to death, but I will say you can do things correctly and have performance issues or the other way and have miscalculations.

Originally Posted by don94403

I completely agree, jmh, when it comes to experienced designers who understand what they are doing and why. I usually don't say that up-front, because the people who usually are reading about normalizing a database haven't yet learned enough to make good decisions about when to denormalize one.

I completely agree. Denormalization is something that must be thought about thoroughly with necessary precautions and is something that probably should be reconsidered when the calculations are to do with people's money

Yup, just to add another voice to the 'exceptions' rule. They are call exceptions becasue, well, they are 'excpetions!'. Follow the rule of normalization unless you have a VERY GOOD REASON for making the exception.

Originally Posted by MindStalker

Actually if you properly index the table, these calculations are fairly instant. The slowdown being the insert for updating the indexes, but no more of a slowdown than a separate update to update the totals with much less corruption possibilities. I've found views to be very good for this too. A lot of people complain about views because unique queries are slower, but whats brilliant about views is they are cached, so a well designed view that is being pulled by each and every user is very very light on the database and is only updated when the referenced table is changed relative to the indexes the view references (if done right).

As I said, it can be argued to death I'd like to think that today's rdbm's are too efficient to have to resort to that kind of denormalization.

And let it be known that I'm a huge fan of using views in SQL Server

EDIT: BTW, welcome to DeveloperBarn

One of the other things that hasn't been hit upon (at least that I could see) as to why storing calculations in a table is not necessarily good is that what if someone goes into the table and changes one of the operands. Since Access doesn't have triggers available, it can't and won't update the calculated field due to that change. So, all of a sudden your data's integrity is challenged.

Just something else to think upon when deciding to store calculated values.