Several decades ago I was a professional programming instructor and I taught client’s employees how to program in a database language named Clipper, and I even wrote a book about Clipper.
Don’t know what Clipper is?
Clipper ran on DOS and is a compiler that compiled a proprietary and extended version of the programming language embedded in the dBase III database management program.
Users of dBase III could write programs and then run them within their copy of dBase III but if they wanted to distribute them to other users they would need to distribute source code, and those other users would need a copy of dBase III to run their programs.
So some really sharp guys created Clipper which a dBase III developer could use to compile their program into an executable file that they could distribute to end-users. Then those end-users could run that Clipper-compiled executable program without having to pay for a dBase III license or even have dBase III installed.
And Clipper is what I once specialized in teaching.
One of my training clients was a regional food distributor in Lacrosse Wisconsin and their manager who was a big fan of Clipper hired me to do (at least) a week of training for his team (the past is fuzzy so I do not remember if it was a single one week trip or multiple trips over time.)
At some point he told me about a project that the “mini computer” team was working on — possibly with a contractor — and that they were having a hard time with it and that he hoped he could convince him to try the project with Clipper, but that they still had to see if the mini-computer team would succeed or not.
About six months later — if memory serves — he hired me to travel back to Lacrosse and to tackle the project with Clipper. So on a plane I hopped.
When I got there they took me to a room with two consultants from Booz Allen Hamilton (BAH) and they presented me with a stack of paper that I remember to be *literally* a foot high. And on each sheet was an allocation formula which they expected me to program into the system I was hired to write. Turns out the project was for me to build an Activity Based Costing system for them.
The idea, the told me, was that it was easy to track profitability of the items the sold. They could just look at how many they sold and what they cost and and that would give them the profitability per item, minus all those pesky overhead expenses.
However, they said, it was much harder to determine how profitable *each customer* was for them. Some of their customers — like those that placed huge orders and were located right down the road — were immensely profitable for them. OTOH, other customers that placed small orders but required delivery a long distance away actually cost them more money to service than they made on them. And my client wanted to figure out which customers were which.
Back to all those formulas. They were accounting allocations, something like this:
“For each customer take the total delivery miles for all deliveries to that customer and then divide by all delivery miles to all customers, then multiply that result by the amount BAH had determined it cost per mile to make a delivery, and finally assign that cost to that customer to later deduct from the profitability calculated for the net margin for all of their purchases.”
Besides that one formula, there were about 1800 other allocation formulas, all of which were either interim results to be used for later calculation, or results used to add to or deduct from the eventually calculated profitability for each customer.
So I remember feeling like a deer in the headlights for three days as I contemplated trying to get my head around all these formulas and realizing that I was probably going to fail at this task just like the mini-computer guys did.
You see the mini-computer guys were apparently trying to write SQL queries for each and every one of those allocation formulas, but maybe with 1800 unique formulas that approach become unmanageable? So that is effectively what I assumed I was supposed to do, but only do it in Clipper. And that was why I expected I was going to fail.
But then I had an epiphany. I did not need to understand all ~1800 formulas I only needed to recognize the patterns, aka the different types of formulas, and then provide the BAH guys with a tool that allowed them to enter those formulas and have those formulas calculated in the order they were needed. In other words, I only needed to do pattern matching and then write code for each pattern that I identified.
So that is what I did.
And from what I heard back from them, they were still using to determine customer profitability 7 years later.
P.S. The moral of this story is for you not to take project requirements presented to you as a given — especially if you come to an impasse when trying to implement the solution — but instead look for patterns and see if you can tackle the project another way, potentially by making your solution more generic.