Calculated Values Stored in Fields Don't Pay, They Cost
Most people who spend a lot of time around relational database applications develop a phobia for calculated values stored in tables. I am aware of arguments for special cases that justify doing it anyway. However, the general rule is a sound one: Don't calculate and store values in tables.I have repeated this rule innumerable times myself. At a recent family gathering, though, I heard a story that explains just how awful the consequences of ignoring this rule can be. I thought I'd share the story here, subject to the usual disclaimers about changing or concealing details to protect privacy of those involved.
This company is in the communications business in a general sense. They install hardware and software along with other supporting infrastructure in order to provide their particular services to both commercial and residential customers. They maintain a number of very large databases, one of which details existing customer accounts, another of which details existing infrastructure previously installed in homes and business locations. In other words, their technical people track where they've installed hardware and other equipment in their area of operations in one database. Their accounting people track customers and the locations where they receive services. Two different databases built around similar, but different data.
Salespersons contact potential customers to negotiate contracts for those services. Part of that process involves requesting information from their technical side about what infrastructure might already be installed at a location, perhaps for a previous occupant of that location. That communication takes some time, slowing down the negotiation. And, as we all know, time is money, especially for a busy salesperson. Somebody came up with the great idea of creating a third system that draws data from both of the other databases, synthesized, of course, for the salesperson. That way, they could provide a simple dashboard style look at any given location that would compare existing hardware and software at a site to the requirement specified in the potential contract at that site and flash one of three or four statuses. Let's say, for the sake of discussion, that "green" means the location needs no modifications to work for THAT contract, "yellow" means minor upgrades or changes, while "red' means the costs of making the required changes would be too great for the potential revenue that would be generated by that contract (unless the customer is willing to pick up the tab).
With that tool in place, a salesperson (or more likely the staff tasked with data entry) can enter the location and the services requested on a potential contract and get a status back right away. They have the option at that point, to sign the contract if it's "green", renegotiate based on a "yellow" status, or inform the potential customer right away that they can't meet their request on "red". Great, saving time means the salesperson moves quicker and earns more. Everyone's happier.
Unfortunately, that status is a calculated value and it is STORED ONLY in the salesperson's system . Not in the technical database and not in the existing accounts database (it's irrelevant to both of them). And of all of the people who use the system, the marketing and sales people are, perhaps, the least likely to understand why that's a problem for them.
In fact, that's where things went off the tracks one day.
A salesperson negotiated a new contract with a potential customer moving into a new space. The proposed contract went back to the office where a data entry person entered it into their sales system. The location and services requested flashed "yellow", meaning it would be possible to accept the contract, but the location would need some modifications. The company was even willing to pay for those minor modifications in anticipation of the revenue the contract could be expected to generate.
The salesperson goes back, explains the situation, gets a new agreement to delay installation until a crew could come and modify infrastructure. In this case, the customer would also have to accept a crew interrupting normal operations. Agreed. That must be a good salesperson, right?
So, the company sends out a sub-contractor to make the modifications. They submit an invoice for the work done and get paid.
Now, the data entry person tries to reenter the new contract, knowing the location now meets the "green" status. Uh, no. The SALESPERSON's system still shows "yellow". What? It seems that the subcontractor was paid, but there was no proof they actually DID the work. Either the infrastructure database was not updated properly, or the subcontractor scammed them. Hm.
So, another delay has to be negotiated with the potential customer while still another crew is sent out to inspect. And YES! the work was done. Good to go, except the keeper of the infrastructure database refuses to update it until they get the subcontractor's written summary of the work. Another delay while they contact the subcontractor to get the required paperwork. The subcontractor tells them it was already submitted along with the invoice, but they'll be happy to go out again to fill out the paperwork. After all, they can't be expected to blindly fill out the required document without an inspection by their own people. And, by the way, they gave the company the only copy of their document. Hm. Double-dipping, perhaps? Super cautious, maybe?
Anyway, the salesperson has a problem. Still another negotiated delay while the paperwork dance is working it's way through the bureaucracy, OR (sly wink), simply change the CALCULATED value from "yellow" to "green", allowing the system to accept the contract so he can get paid. And that's what happened. All happy now, right?
Well, not quite yet. The salesperson sets a date for the final install, gets his commission check, and moves on to the next sale.
Not so fast. When the company's install crew arrives on site to do the final work, THEIR database still shows deficiencies. And they refuse to install. Everyone insists it's all good, but if you get in the habit of overriding your system, sooner or later you're going to be sorry. No install until they get the document that shows the required work was done.
The salesperson has no choice but to ask the potential customer to delay once more and allow another crew to come out and verify their first crew did their work. At this point, the customer said, "enough is enough" and the deal was cancelled. The commission had to be clawed back in the salesperson's next check.
So, this story involves a number of problems, and the storage of a calculated value is just one. The subcontractor may, or may not, have been playing fair. The bright bulb who adjusted the status from "yellow" to "green" probably didn't even know the trap they had just set. A combination of rigid rules and the need for accountability combined to sabotage things. But none of it would have happened except for that short-cut that allowed the calculated value to be stored--and updated manually.
