Discuss "Store Calculated Values (Updating Fields)" in the Microsoft Access forum.

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.

Attached Files

storeval.zip (41.4 KB, 13 views)

Hi, ben, this is califdon (aka don94403!)

Of course I agree with you completely, but I wanted to add my slant on it: the strongest reason why storing calculated values is bad is that it violates the 3rd Normal Form rule of normalization, which states that no non-key attribute should be dependent on any other non-key attribute. You will find some database practitioners who will argue that this is carrying things too far. But whose opinion means more to you, some unknown database developer, or Dr. Ted Codd and Chris Date, who developed relational database theory and wrote the primary textbook that is still used around the world, 8 editions and 40 years later?

Your explanation is a good down-to-earth explanation and is probably more useful to most beginners, but I just thought I'd go on the record with the formal explanation.

I agree completely Don. I was kinda counting on you to give the more formal explanation . I merely wanted to make simple db to show how it could fail those who try to store calculated values I tried to rep ya, but it says I need to spread it around first

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.

__________________
jmurrayhead
Did I help you out? Make me popular by clicking the icon!

If you found a post helpful, please click the button in the lower right-hand corner of the post.

Powered by ASP.Net

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.

__________________
Wolffy
------------------------
Opinions expressed are my own and do not necessity reflect those of any sane person. Any code provided is intended to be an example and is provided AS IS. Rework for your specific environment may be required. Void where prohibited by law. Not valid in California. Your mileage may vary.