Stored Calculations (1 Viewer)

[ChrisO]

Just to clarify my position…

It seems that people are confusing the words ‘Use’ and ‘Create’.

I would not create a field name called ‘Name’ but if I didn’t create it and if I couldn’t change it, then I would use it.

But we still see comments like “You should not/must not ‘use’ reserved words as field names.”
Under the circumstances that a person did not create the name and can’t change the name then that statement is incorrect.


Then the statement “You should not/must not ‘use’ reserved words as field names.”
Can de-generate into, sorry Brian but it’s that ‘use’ word again: -
>>Anybody who does not think that the use of reserved words is crazy is either inexperienced or crazy themselves.<<

It is not crazy to use the things we can’t change but it might be crazy to create them.
I say might be crazy to create them because at the time of creation they may, of at that time, not be reserved words but have subsequently become so.

Regards,
Chris.

 

[Brianwarnock]

Sorry Chris but that was like reading a post by Col or Rich, you're playing symantics, obviously if you are working on a DB that all ready exists then you may have to go with it, infact so obvious that nobody would have factored it into the discussion.

Brian

 

[Rabbie]

Thank you Brian! I couldn't have put it better myself. That's why I stressed Should not rather Must not. There are very ew absolutes bt why make things more difficult than they need to be. These things often cause more problems for the people maintaining the DB later on. What happens in System tables is largely a matter for microsoft but let's advise people writing their DBs of the potential problems of using reserved words as data names

 

[ChrisO]

Brian.

Obvious? Symantics? Perhaps, but that is the business we are in.

Obviously there is a difference and I think we should be mindful of the difference when speaking to others.
This thread is in an open forum which people, from all around the world with all levels of skill, can read.

So I think we should be careful when making generalized statements in an industry which tends to be rather semantic.

I’m hoping this thread will lead to the imbedded idea that: -
1. Don’t create fields using reserved words.
2. If you have then change it.
3. If you can’t change it, learn how to handle it.

That, to me, is a long way from the generally stated rule of: -
“Don’t use reserved words as field names.”

 

[Rabbie]

Chris, I understand your point but I think that for people with little or no experience it is easier to follow 1 rule than 3. IMHO as soon as you open a loophole people rush like lemmings to use it.

 

[ChrisO]

Rabbie.

The use of lemmings is propagating a myth.

I think we can explain things to people as and when needed.

Regards,
Chris.

 

[Rich]

Sorry Chris but that was like reading a post by Col or Rich, you're playing symantics, obviously if you are working on a DB that all ready exists then you may have to go with it, infact so obvious that nobody would have factored it into the discussion.

Brian

Are you suggesting that my posts on the main boards are somehow flawed, is it only now open to a select few, who themselves often get so caught up in semantics that they miss the obvious

 

[Brianwarnock]

I think we can explain things to people as and when needed.

Regards,
Chris.

Precisely, that is why I will continue to say "use" on the grounds that the person i am talking to is intelligent, if they then come back and state that the words are already in use in the DB and cannot be changed I will remind them to use [] at all times etc etc.

But I will not clog up threads with a long discussion and numerous rules.

Brian

 

[Brianwarnock]

Are you suggesting that my posts on the main boards are somehow flawed, is it only now open to a select few, who themselves often get so caught up in semantics that they miss the obvious

I was suggesting that you do use semantic arguments to make points, but don't ask me for an example.

Brian

 

[Rich]

I was suggesting that you do use semantic arguments to make points, but don't ask me for an example.

Brian

Not on the main boards Brian without good reason, like arguing that most calculations can be carried out at Report level rather than having to use Queries for the same purpose, but then I've always found the former method to be faster, semantics or personal experience, which is now the best?

 

[RainLover]

In my original post # 1, I said it was for a bit of FUN. But most people read what they wanted to read and not the real intent. (FUN)

I was expecting someone to say, “I never store calculated Values”

I would have then said, “Hey what about AutoNumber or DMax Plus 1”

Just a bit of FUN.

But because some have decided to take this discussion beyond what it was intended to do, I shall attempt to explain my philosophy with regards to, not adhering to normalisation rules, rather than blatantly breaking those rules.

Let me say up front that whenever I design a Database I am very strict with regard to normalisation. I do not create Fields that are Reserved Words, I do not use Spaces or Special Characters and I maintain what I feel is good Naming conventions.

To address the Storing Calculated Values.

This practice is to be frowned on in general circumstances. There is an argument that if an underlying value was to change then the calculated value would then be incorrect. The inverse argument is that if an underlying value was changed then the stored Calculated Value should alert you to this fact, and that you should investigate the cause.

Storing of Totals in an Invoice is a Must as this has a Tax implication.

Storing of Invoice Numbers is also a must as we need to refer to the correct invoice number.

Stock takes are also a store calculated value. (What quantity did we have on hand at the end of the year?)

When I buy a carton of Milk from my local grocery chain (Woolworths in my case) do you think they calculate the cartons left in the fridge? Total purchased in last 10 year minus total sold in last 10 years. I don’t think so.

So when I see someone answer an OP’s question by critising them for their table strustructure without asking for a proper understanding, then I feel that the person answering the post is most often quoting rhetoric that they have read before without taking the time to understand the OP’s situation.

 

[Rich]

When I buy a carton of Milk from my local grocery chain (Woolworths in my case) do you think they calculate the cartons left in the fridge? Total purchased in last 10 year minus total sold in last 10 years. I don’t think so.
.

They would actually carry out regular stock takes and store that figure

 

[Galaxiom]

.
Storing of Totals in an Invoice is a Must as this has a Tax implication.

Storing of Invoice Numbers is also a must as we need to refer to the correct invoice number.

An invoice is a fixed transaction. None of the item prices, the line subtotals or the grand total can change so there is no harm in recording the grand total. It will expedite the the display of sales summaries and provide an integrity check on the invoice to ensure no corruption.

Personally I probably would not store an invoice line total at all. Although necessary to display on the invoice it is messy. Using a control bound to the record source would require an update to the value using code when the quantity or price changed during the creation of the invoice. With a control source as the calculation this update is automatic.

Stock takes are also a store calculated value. (What quantity did we have on hand at the end of the year?)

When I buy a carton of Milk from my local grocery chain (Woolworths in my case) do you think they calculate the cartons left in the fridge? Total purchased in last 10 year minus total sold in last 10 years. I don’t think so.

Systems that decrement and inventory quantity at every transaction are notoriously unreliable particularly in multiuser environments. The calculated stock is the way to go. However one would not tally from the opening of the system but refresh an opening stock quantity figure at a convenient time depending on the nature of the business. That might be during the end of procedures. Transactions would be summed to a particular moment and the opening stock quantity updated.

 

[Brianwarnock]

Not on the main boards Brian without good reason, like arguing that most calculations can be carried out at Report level rather than having to use Queries for the same purpose, but then I've always found the former method to be faster, semantics or personal experience, which is now the best?

I wasn't thinking of the main boards where I have no complaints about your posts, infact I have no serious complaints about your posts anyway, I can always ignore a post even if some can't. The semantics comment was not so much a criticism as an observation. People who are losing an argument often start to get into semantics.

Brian

 

[gemma-the-husky]

just on the subject of invoices, this does present an immediate issue, invoving tax rounding errors

if you calculate the line totals of invoices, and calcluate the sales tax per line, then quite often this will be a different (by pennies) from totalling the line totals and evaluating the tax on the overtall total.

now neither way is incorrect - its just the enterprise rule you adopt.

and you dont HAVE to store these totals, as long as recalculating them will produce the same result each time. Its just that a lot of systems will store these prticluar totals - and will also then create a separate invoice record in the sales ledger, that ALSO stores these same totals.

A lot of this is because many real world accounting systems dont actually harness all the power of a RDBS, and DO opt to both store calculated values, and maintain whole records derivable form other structures - probably because many of them are developments of older 3GL file based systems

Of course the downside of never storing calculated transactions or values, is that when you do reach some practical size limits, you can run into some real problems, about how and what to delete to recover some space.


As an example, I bet many systems implement VAT changes by having a single VAT record that stores

current rate, date
old rate, date

and use a mechanism to examine this record to establish the correct tax rate - rather than having a true table of VAT rates.

The problem is that at some point the historic VAT rates MAY fall of the end of the finite single record VAT structure, and the historic rate then ceases to be avaialble at all - so it needs ot be stored permanently

 

[gemma-the-husky]

just on the subject of invoices, this does present an immediate issue, invoving tax rounding errors

if you calculate the line totals of invoices, and calcluate the sales tax per line, then quite often this will be a different (by pennies) from totalling the line totals and evaluating the tax on the overtall total.

now neither way is incorrect - its just the enterprise rule you adopt.

and you dont HAVE to store these totals, as long as recalculating them will produce the same result each time. Its just that a lot of systems will store these prticluar totals - and will also then create a separate invoice record in the sales ledger, that ALSO stores these same totals.

A lot of this is because many real world accounting systems dont actually harness all the power of a RDBS, and DO opt to both store calculated values, and maintain whole records derivable form other structures - probably because many of them are developments of older 3GL file based systems

Of course the downside of never storing calculated transactions, is that when you do reach some practical size limits, you can run into some real problems, about how and what to delete to recover some space.



just to reinforce galxiom's point on stock systems in the last post - if you calculate the stock "on the fly" each time, you never have to worry about the effect of adding/modifyingdeleting transactions. Deleting in a RDBS is particularly tricky, as there are often many ways of deleting items, and if you were trying to maintain a stock count, you might find it difficult to trap every one of them - eg, something as unusual as just opening a table, and deleting a record manually for some reason.

 

[Galaxiom]

I've just seen too many posts here where someone used NAME as field name and they were having problems and renaming the field fixed it.

I suspect in every case that they would have been using Name without the square brackets. I would expect that to cause trouble.

 

[stopher]

In my original post # 1, I said it was for a bit of FUN. But most people read what they wanted to read and not the real intent. (FUN)

I was expecting someone to say, “I never store calculated Values”

I would have then said, “Hey what about AutoNumber or DMax Plus 1”

I don't think that's the same thing. Autonumber and DMax are techniques to generate a new unique number typically for use in a primary key (and hence good relational design). If you want to stretch the point further you might argue that DATE() is a formula and therefore calculated data!

To address the Storing Calculated Values.

This practice is to be frowned on in general circumstances. There is an argument that if an underlying value was to change then the calculated value would then be incorrect. The inverse argument is that if an underlying value was changed then the stored Calculated Value should alert you to this fact, and that you should investigate the cause.

Which would expose a serious problem with your database. Imagine if an auditor found an invoice where the values didn't tie up. What would your response to the auditor be? "Must be a glitch in my database"? The auditors response will be "it's going to be a long night then..."

There lies the fundamental problem. If you are going to to store derivable data then you must be 100% (and I do mean 100%) sure that your database is able to enforce that. In Access this is, in principle, achievable e.g. using events etc. But Access comes with a lot of "what-ifs". What if the PC crashes before the update? What if someone decides to update directly to the table but doesn't realise the Total also has to be updated? e.g. the VAT was wrong but it's ok we can correct with an Update query because we haven't published the invoices yet. What if a new designer comes along and designs another form that updates tables but doesn't realise the original designer had been storing calculated data?

I appreciate all these can be overcome by professionals and experienced users but most of the questioners to these forums are not at that level. To allow novices go down this road in preference to the "good practice" of not storing calculated data is, imho, dangerous. By the same token you could say Normalisation is not necessary (as Dave "the husky" pointed out, many 3rd party systems don't implement hard normalisation), but is this a good grounding for a novice to build database? Far better to encourage practices which makes the process of designing consistent database easier to undertake.

So when I see someone answer an OP’s question by critising them for their table strustructure without asking for a proper understanding, then I feel that the person answering the post is most often quoting rhetoric that they have read before without taking the time to understand the OP’s situation.

I assume you mean questioning their table structure rather than actually criticising them?(I don't think I've ever seen OP's criticise people personally simply because their design was flawed).

I think this is off the topic the that you are keen to have discussed (a worthy topic by the way), but this is how I see it. These are public forums where OP's contribute for free. In doing that they inevitably want to limit their time spent responding. These often means that assumtions have to be made in order to at least start to address the question. It would be unreasonable to expect an OP to do a full analysis of the users requirements before feeling they could give the correct answer.

Furthermore, when I read a post, the first thing I ask myself is "is the table design right?". If there are any clues to suggest they are not following good design practice I will point these out regardless of whether it's answering the question or whether they may be in the 5% of cases where they are justified. It encourages the user to reflect on their design.

Contrast this to the approach where I were to simply answer the question and don't challenge the potential design flaws. This certainly solves their current problem quickly and neatly in the short term but spells potential disaster for the user in the long term.

Chris

 

[The_Doc_Man]

I think I can honestly say I have NEVER designed an Access database that ever used a reserved word.

However, that is true for a very good reason. Access and VBA aren't my first programming language environment. FORTRAN II-D was. Try to use a reserved word in THAT context and it'll eat your socks because that language didn't have a method of disambiguation. So I learned the reserved-word lesson earlier and haven't broken that rule since.

Now I work in a government environment where naming rules exist. It would be a positive PLEASURE to have the latitude to use a field name as short as Name, but those days are long gone for me. So I still avoid using reserved words, most of which are fairly short anyway. Using them now would break TWO rules. Oh, that old adage about "better to seek forgiveness than permission" doesn't work in the government. It took me a couple of years to figure out why. Forgiveness isn't a contract line item so cannot be properly charged on your time sheet or contrator billing invoice.

As to storing computed data, I do it when there is a pragmatic reason to do so. My usual reasons for such heresy involve the need to run reports that occupy literally 1/2 Gigabyte. They take a couple of hours to run. I compute and store values on the fly because I can only afford to re-run that puppy once per week. In between, I have to rely on the stored value. However, just to be meticulous about it, I also store the date on which that compute value was valid. Then, when I report something, I include the age of the computation as part of the report. Yeah, I know - not required for most cases, but in my case, data age becomes an issue.

And RainLover, if you read my response with the right attitude, you would recognize that I really AM trying to have some fun with it.

 

[Rabbie]

Rabbie.

The use of lemmings is propagating a myth.

I think we can explain things to people as and when needed.

Regards,
Chris.

I am sorry if I you failed to understand I was referring to the lemmings of mythology rater than the real ones. Having lived and worked in Scandinavia for 10 years I am perhaps better acquainted with lemmings than you However my experience as a trainer of programmers leads me to belive the people are always very eager to exploit any loophole in rules. And as we are all agreed it is much easier not to do something in the first place than to removed it from the design later.