There are important reasons not to save calculated values. There are risks of data anomalies by creating, changing and deleting the basic data for calculation, and there are redundancies. Some also call this the 0th normal form.
@ebs17 You don't need to lecture me about normal forms. I understand why we don't
generally store calculated values and I explained the reason for storing this one. I'll do it again. There is NO import of data that would generate new records. If there were, it would have to use a function to call the same generation code as is used by the data entry form. That means, It is always generated programmatically when a new record is entered. There is no other source for the calculation than the ONE data entry point of a new record. To the user, it is the ID for one record even though behind the scenes, there is an autonumber that serves the purpose of PK. In fact, the generated "key" should never be used as the PK. Once it is generated it cannot be modified. Just as the user cannot change the PK autonumber, the user cannot change this value. Why you would ever want to spread this type of calculation to multiple places is beyond me. Storing the generated number is specifically done to eliminate ever having to have that particular calculation any other place but one! Storing the full string does not prevent in any way the proper use of the constituent parts. What it prevents is having to decompose the "intelligent key" key for any reason. Think of this as a safety play. If the generation method ever has to change, you have one and only one place to modify the code. If one of the constituent parts changes, the "intelligent key" doesn't automatically change.
I'll give you a real world example. Social Security number in the US. I'm talking about the problem from inside the SSA, not how the SSN is used by employers. It was a 9 digit number. The first three digits were a geographic key and the next two digits were a group. That left the last four as a serial number. As population grows, the number of numbers available within a three digit geographic area and two digit group became constrained, the SSA had to change their method of assignment. If they were generating/decomposing this field throughout their application (it was and may still be COBOL, btw), they would have had to locate all the calculation spots and change them to lookups. Another problem is running out of numbers. Even though the SSN can support a billion numbers and the US population is just a little more than 1/3 of that, over time, the numbers will run out and reuse isn't really an option. So, either the number will be lengthened or letters will be introduced changing it from a number to a string. So, temporarily, the SSA changed to a randomized method of assigning the number so you can no longer look at an SSN and make any assumptions as to meaning. In the past couple of years the SSA has been issuing new SSN's to all original SSN holders that now include alpha characters. That "number" is to be used going forward.
Now from the perspective of companies who have to store the SSN, some made the mistake of storing the code as a number rather than text. With the use of leading zeros, that becomes problematic since numeric data types do not store leading zeros. You can overcome it with formatting but if you are using string functions to decompose the "number", they will no longer work with leading zeros unless you use the Format() function in all expressions. But, now that SSN contains alpha characters, storing the SSN as a number, is no longer possible so any company that made that mistake has to change the data type of the SSN to hold the new alpha code.
The SSA eliminated the geographical significance of the first three digits of the SSN, referred to as the area number, by no longer allocating the area numbers for assignment to individuals in specific states. The significance of the highest group number (the fourth and fifth digits of the SSN) for validation purposes was eliminated. Randomization also introduced previously unassigned area numbers for assignment excluding area numbers 000, 666 and 900-999. The SSA implemented the new assignment methodology on June 25, 2011
So, technically, storing or not storing the generated number is a choice. It is not wrong, nor is it right. It is a matter of risk avoidance. If you know for a fact that the generation method will never, ever have to change, then calculate it everywhere you want. If you don't care if the number changes if one of the underlying parts changes, live with it.
The prohibition against storing calculated values is to protect against anomalies when the underlying data changes. In this particular case, if the underlying data changes, you DO NOT want to generate a different code. What if the client wanted to use state as part of the "key", are you going to say NO? If you don't, you run the risk of the "unique custom key" changing on the fly if the customer moves. How about if you use a couple of letters of the user name as part of the "key". What if the name changes? Do you really want to all of a sudden have a new ID? I don't think so. You also can't just slip in the replacement state or other string, you actually have to generate a new serial number for consistency and to avoid potential duplication.
Yes, we don't want to store the customer's outstanding balance in the Customer table. But, when changes to the underlying data should NOT cause a change to the calculated value, you store the calculated value. You are applying a generalized rule to a specific situation to which it should not be applied.
At best, I get to discourage the practice. But, users just don't like simple numbers, especially if they've been doing it this way forever.
