Stored Calculations (1 Viewer)

RainLover

VIP From a land downunder
Local time
Tomorrow, 00:57
Joined
Jan 5, 2009
Messages
5,041
I don't want to be accused of hijacking a thread so I thought I should start a new one.

And this is just for a bit of fun so please do not get offended.

My big question is;

Can anyone say that they never store a calculated value?

My bet is that we all do, somewhere sometimes, and that that is totally acceptable.
 

ajetrumpet

Banned
Local time
Today, 09:57
Joined
Jun 22, 2007
Messages
5,638
I've stored so many, I need to count them on ................. fingers. :D
 

RainLover

VIP From a land downunder
Local time
Tomorrow, 00:57
Joined
Jan 5, 2009
Messages
5,041
Thank you Adam.

Obviously your knowledge has been gained from experience.
 
Last edited:

Mike375

Registered User.
Local time
Tomorrow, 00:57
Joined
Aug 28, 2008
Messages
2,548
I store the ones that have to be stored like commissions where the rate can change.

But I also store some where they don't need to be stored.

I also store in a lot of my Many tables the persons base details like their name, phone number etc.It does not worry me if a table has a few extra fields and there are a few extra lines of SetValues
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 00:57
Joined
Jan 20, 2009
Messages
12,853
Guilty. I store results from elaborate queries that take a couple of minutes to run. These results are generated monthly and need to be reviewed in conjunction with reports on subsequent months. Consequently not storing the results could make for an intolerably slow experience for the user.

However the underlying data cannot be altered so there is no danger of ever generating conflicting reports.

Eventually I expect to discard some of the original data. However I am still waiting for evidence that the number of records is causing a problem. The main table has just reached four million records across sixteen fields. The ability of databases to handle so much information is really quite remarkable.
 

ChrisO

Registered User.
Local time
Tomorrow, 00:57
Joined
Apr 30, 2003
Messages
3,202
Galaxiom, that’s not being guilty, that’s just being practical.

I store calculated values for fast mouse move totals. No real point recalculating a total project cost simply to satisfy some strange notion that some self-proclaimed guru has said about…whatever.

For the same reason, speed, why re-calculate from the ground up if what we really want is an ageing summary Form or Report?

If we had a database that stored results from experiments so that people could verify those results would we throw out the results because they were calculated?
 
Local time
Today, 09:57
Joined
Mar 4, 2008
Messages
3,856
Thank you Adam.

Obviously you knowledge has been gained from experience.

Why is it obvious? What if his answer had been different? Is it obvious because you agree with him or because of his witty response?

BTW, (IMHO) storing calculations is OK when you design your system correctly. Many of the questions we get on AWF where the user wants to store calculations, the poster obviously has not put much thought into the overall design of their system and some of us automatically say not to do so. But I'm guessing that in practice, those of us who are veterans design our systems to take advantage of storing calculation results.

Don't even get me started on the difference between OLTP and DSS/reporting scenarios.
 

ChrisO

Registered User.
Local time
Tomorrow, 00:57
Joined
Apr 30, 2003
Messages
3,202
In process control systems do we log pipe bore diameter, orifice plate diameter, upstream and downstream tapping distances, upstream and downstream straight pipe distances, and if a gas then its absolute pressure, flowing density, flowing temperature and current head across the tapping points? Or do we just log the calculated flow?
 

RainLover

VIP From a land downunder
Local time
Tomorrow, 00:57
Joined
Jan 5, 2009
Messages
5,041
Interesting.

Not one person can honestly say that they do not store calculated values.

So shall we say "Never say Never" ?

PS Next thing you guys will be saying is that you have Never created a Database that uses the word "Name" as one of the fields in a table. :)
 

namliam

The Mailman - AWF VIP
Local time
Today, 16:57
Joined
Aug 11, 2003
Messages
11,695
Guilty. I store results from elaborate queries that take a couple of minutes to run. These results are generated monthly and need to be reviewed in conjunction with reports on subsequent months. Consequently not storing the results could make for an intolerably slow experience for the user.
This isnt storing calculated values, this is creating a datamart to store collated information.
True in definition it is storing a calculated value, but I do not consider it so.

A calculation is a simple and fixed calculation:
Feild1 + field2
Date2 - date1
etc....

Changing data like VAT calculation or commisions is debatable, but a datamart is IMHO not calculated values.

The "never store a calculated value" is a rule of thumb, which is true for 99+% of all situations.
A rule of thumb is not a beat all must adhere to kind of rule. Heck even the "every table must have a PK" is "optional" but still very wise
 

RainLover

VIP From a land downunder
Local time
Tomorrow, 00:57
Joined
Jan 5, 2009
Messages
5,041
namliam

You statement is in agreement to my way of thinking.

There are some basic Rules which we should all adhere to. But then there is the odd occassion when we should break away from the Normal way of doing things because of certain circumstances.

Thank you for taking the time to reply.
 

ChrisO

Registered User.
Local time
Tomorrow, 00:57
Joined
Apr 30, 2003
Messages
3,202
So, Des, why do some people say never do it?
Is that just laziness on the part of the poster?
 

RainLover

VIP From a land downunder
Local time
Tomorrow, 00:57
Joined
Jan 5, 2009
Messages
5,041
Chris

I also thank you for your reply and in particular the manner in which you challenged me to justify my opinion.

We often read in these Forums the Rules of NEVER do this and ALWAYS do something else.

I found this old story on the web.

There’s an old joke, so old that I don’t even know for certain where it originated, that’s often used to explain why big corporations do things the way they do. It involves some monkeys, a cage, a banana and a fire hose.
You build a nice big room-sized cage, and in one end of it you put five monkeys. In the other end you put the banana. Then you stand by with the fire hose. Sooner or later one of the monkeys is going to go after the banana, and when it does you turn on the fire hose and spray the other monkeys with it. Replace the banana if needed, then repeat the process. Monkeys are pretty smart, so they’ll figure this out pretty quickly: “If anybody goes for the banana, the rest of us get the hose.” Soon they’ll attack any member of their group who tries to go to the banana.
Once this happens, you take one monkey out of the cage and bring in a new one. The new monkey will come in, try to make friends, and then probably go for the banana. And the other monkeys, knowing what this means, will attack him to stop you from using the hose on them. Eventually the new monkey will get the message, and will even start joining in on the attack if somebody else goes for the banana. Once this happens, take another of the original monkeys out of the cage and bring in another new monkey.
After repeating this a few times, there will come a moment when none of the monkeys in the cage have ever been sprayed by the fire hose; in fact, they’ll never even have seen the hose. But they’ll attack any monkey who goes to get the banana. If the monkeys could speak English and if you could ask them why they attack anyone who goes for the banana, their answer would almost certainly be: “Well, I don’t really know, but that’s how we’ve always done things around here.”
This is a startlingly good analogy for the way lots of corporations do things: once a particular process is entrenched (and especially after a couple rounds of employee turnover), there’s nobody left who remembers why the company does things this way. There’s nobody who stops to think about whether this is still a good way to do things, or whether it was even a good idea way back at the beginning. The process continues through nothing more than inertia, and anyone who suggests a change is likely to end up viciously attacked by monkeys.
But this is also a really good analogy for the way a lot of software works: a function or a class or a library was written, once upon a time, and maybe at the time it was a good idea. Maybe now it’s not such a good idea, and actually causes more problems than it solves, but hey, that’s the way we’ve always done things around here, and who are you to suggest a change? Should I go get the fire hose?
There’s an old joke, so old that I don’t even know for certain where it originated, that’s often used to explain why big corporations do things the way they do. It involves some monkeys, a cage, a banana and a fire hose.
You build a nice big room-sized cage, and in one end of it you put five monkeys. In the other end you put the banana. Then you stand by with the fire hose. Sooner or later one of the monkeys is going to go after the banana, and when it does you turn on the fire hose and spray the other monkeys with it. Replace the banana if needed, then repeat the process. Monkeys are pretty smart, so they’ll figure this out pretty quickly: “If anybody goes for the banana, the rest of us get the hose.” Soon they’ll attack any member of their group who tries to go to the banana.
Once this happens, you take one monkey out of the cage and bring in a new one. The new monkey will come in, try to make friends, and then probably go for the banana. And the other monkeys, knowing what this means, will attack him to stop you from using the hose on them. Eventually the new monkey will get the message, and will even start joining in on the attack if somebody else goes for the banana. Once this happens, take another of the original monkeys out of the cage and bring in another new monkey.
After repeating this a few times, there will come a moment when none of the monkeys in the cage have ever been sprayed by the fire hose; in fact, they’ll never even have seen the hose. But they’ll attack any monkey who goes to get the banana. If the monkeys could speak English and if you could ask them why they attack anyone who goes for the banana, their answer would almost certainly be: “Well, I don’t really know, but that’s how we’ve always done things around here.”
This is a startlingly good analogy for the way lots of corporations do things: once a particular process is entrenched (and especially after a couple rounds of employee turnover), there’s nobody left who remembers why the company does things this way. There’s nobody who stops to think about whether this is still a good way to do things, or whether it was even a good idea way back at the beginning. The process continues through nothing more than inertia, and anyone who suggests a change is likely to end up viciously attacked by monkeys.
But this is also a really good analogy for the way a lot of software works: a function or a class or a library was written, once upon a time, and maybe at the time it was a good idea. Maybe now it’s not such a good idea, and actually causes more problems than it solves, but hey, that’s the way we’ve always done things around here, and who are you to suggest a change? Should I go get the fire hose?


I like to challenge people to think, not merely quote what has been said time and time again as though it was gospel.
 

stopher

AWF VIP
Local time
Today, 15:57
Joined
Feb 1, 2006
Messages
2,395
So, Des, why do some people say never do it?
Is that just laziness on the part of the poster?
I certainly agree that "never" is too strong a word and may be used inadvertently without due reference i.e. people may say “never” but actually mean “in 99.9% of cases”. Maybe that’s a caution to respondents to look at how their response might be perceived. However I think it’s as much about ensuring people understand theory and good practice before being enlightened to the exceptions.

I do think that the principle of not storing calculated data is one that should be encouraged, particularly in forums where folks posting problems do not fully understand the concepts of good database design and the issues with storing calculated data. Novices, quite naturally think that storing calculated data is the logical thing to do particularly if they have been used to designing spreadsheets. But of course the same is not true of relational databases where we are trying to eradicate any inconsistency. There may be exceptions where the novice may say "are but....." in which case they may have a valid reason for storing calculated data which can be discussed. But the number of cases of this is rare compared to users who are just doing bad design. My worry is that once you start suggested to a user that they can store calculated data under some circumstances is that you are opening the door to users for those cases where it's just not appropriate.

To answer the original question, I also store calculated data where appropriate. I have done so for
1. Datamarts/Datawarehouses
2. Temporary tables for "difficult" or slow reports
3. Pricing

In more detail:
1.
Datamarts/datawarehouses are generally non-normalised and therefore aren't defined by the same rules as a relational database and therefore aren’t breaking the rule. My datawarehouses are not editable so the base and calculated data is always consistent. Records are refreshed in their entirety or not at all.

2.
I see temporary tables purely as a tool for aiding the running of reports and not part of the core database. I have managed to avoid storing calculated data indefinitely for general forms/reports mainly because I've managed to constrain my stored calcs to datawarehouses. However, in my current project I'm getting perilously close to having "permanent" calculated data due to the length of time the calcs take to run. My big worry is that the base data gets entered/updated throughout the day but the calcs only get updated once per day (due to the length of time to run) - hence inconsistency.

3.
Pricing is the age old question. I have no problem with storing prices from a price lookup. My view is that the copied price is the price at a fixed point in time for a specific document whereas the lookup is a generic i.e. they are not the same and thus do not break any rules. One could argue that this should always be consistent with the lookup table but in practice this is not the case. There could be many reasons for amending the document price and you only need one price to be amended to prove that the lookup price is in fact "different" to the price stored in the document. As to storing Price, Qty and Price x Qty - I just don't and would only find this acceptable where there are performance issues to deal with. I only store Price and Qty.

Chris
 

stopher

AWF VIP
Local time
Today, 15:57
Joined
Feb 1, 2006
Messages
2,395
Chris

I also thank you for your reply and in particular the manner in which you challenged me to justify my opinion.

We often read in these Forums the Rules of NEVER do this and ALWAYS do something else.

I found this old story on the web.



I like to challenge people to think, not merely quote what has been said time and time again as though it was gospel.
Great story and one that exemplifies Socratic philosophy – particularly the notion of challenging the status quo (didn’t Socrates eventually die for this belief?!).

On another philosophical level (I hope this isn’t too deep), take a look at Plato’s “Allegory of the Cave”. Are we just looking at shadows (accepting that that’s how it is)?
http://www.word-gems.com/philos.cave.html

For me, I think we do follow some rational reasoning and understanding on why we don’t store calculations rather than just accepting it (I’m talking in general rather than the exception). But of course in the true spirit of philosophy, my our reasoning and rational is open to be challenged.

Chris
 

DCrake

Remembered
Local time
Today, 15:57
Joined
Jun 8, 2005
Messages
8,632
There is also a difference between calculated fields and relational fields. Take the instance of an invoice table. Instead of having the customers PK as an FK some people store the customer name to save creating a lookup or join in the invoice queries. This may be fine if company names do not change, but likely one will at some point in time. Thus there becomes a break in the referertial integrity between the customers table and the invoices table.

Also in the UK we recently underwent a short period of VAT reduction form 17.5% to 15.0%. Now back to 17.5%. Now if we had a link between the VAT Rates table and the Invoice and used calculated fields the invoices created whilst at 15% would be wrong. So it is prudent to store the elements of the equasion in the table, such as VAT Code, VAT Rate, Net Amount, Etc and perform the calculation dynamically. Alternatively we could store The net amount of the invoice the vat amount, the gross amount, shipping, etc and curtail any calculations.

Again this is fine for single currency systems, what about multi currencies, exchange rates, etc? The need to store computations would far outweigh the need to build in some sort of historic costing module to contend with any unforseable changes that may or may not occur.

David
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 15:57
Joined
Sep 12, 2006
Messages
15,660
right

i think it is worth storing a calculated value when

a) the value is permanent, and will not change (and is often a prime figure - eg an invoice total) and
b) the effort of recalculating/looking it up is either excessive, or (especially) there is a possibility that in the future, the information may not be available

probably other things as well, but these are the ones that strike me as ideal candidates.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 00:57
Joined
Jan 20, 2009
Messages
12,853
Instead of having the customers PK as an FK some people store the customer name to save creating a lookup or join in the invoice queries. This may be fine if company names do not change, but likely one will at some point in time. Thus there becomes a break in the referertial integrity between the customers table and the invoices table.

The reverse case using only the CustomerID with a single record for their name is also a problem when the name changes.

An invoice is to a company or person that has a particular name. It would be wholly unacceptable for an old invoice record to display a subsequent change in the customer name.

However storing the same name on every one of hundreds or perhaps thousand of invoices is not always a good idea.

Instead a NameHistory table can be used. This records a name against the CustomerID key and the implementation date of the change. The invoice query pulls the appropriate name according to the date of the transaction. The same can be done for tax or price. This minimises the storage of repeated data while maintining proper normalization and referential integrity.

However in the case of price, the salesperson needs to be able to vary the price on the invoice so it does need to be stored in the invoice line record. (Image turning down a sale when the customer is comparing with a competitors price. "Sorry, changing the price would breach normalisation of our database." :D)
 

boblarson

Smeghead
Local time
Today, 07:57
Joined
Jan 12, 2001
Messages
32,059
PS Next thing you guys will be saying is that you have Never created a Database that uses the word "Name" as one of the fields in a table. :)
I did use it a long time ago when I was first starting out. And I paid the price for it. As for Access Reserved Words being used as field or object names, I would say that NAME is probably the worst one that can be used because it can affect much more than most Access Reserved Words. Most of the Access Reserved Words will affect one thing and not much else. But, NAME on the other hand is one where EVERYTHING in the database has a NAME property and there are uses of NAME which, if used as a field name, can honk that up, even with the use of square brackets. An example is if you want to display the name of a report in a text box. You can use =[Name] to get it, but NOT if you use Name as a field. It will honk that up.

So, anyway, as far as calculated fields go, I know that many people are not liking the fact that Access 2010 has calculated fields in it. But it does and you can make use of those to ensure the calculations stay up to date if, and when, any field data is changed. And also Data Macros can help with keeping things synched up if you need to (triggers in Access basically). So, for many occasions it might be right to not store the calculations, it is really not going to hurt anything at all if done in Access 2010, where it could potentially be dangerous in previous versions; regardless of one's stance on normalization.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 00:57
Joined
Jan 20, 2009
Messages
12,853
I did use it a long time ago when I was first starting out. And I paid the price for it. As for Access Reserved Words being used as field or object names, I would say that NAME is probably the worst one that can be used because it can affect much more than most Access Reserved Words

I used to use reserved words without any issues but learnt the error of my ways. Figured there must be something in it when so many warned. Better sure than sorry. It is as much a good practice from promoting readability as avoiding conflict.

Actually I think Date is worse than Name. I have seen a case where the Date() function refused to keep its brackets, apparently preferring to refer to the field.
 

Users who are viewing this thread

Top Bottom