Management will frequently hire young developers just out of school because a) they're cheap, and b) a developer is a developer is a developer. Graduates, especially from advanced degree programs, always have more advanced training than those with lesser degrees, and should be able to bring advanced skills to the table on day-1. Sometimes management gets lucky, and with a bit of proper guidance and oversight, the newbie can create something reasonably functional, performant and maintainable. This is not one of those occasions.

In the aftermath of that strategy when management realizes that perhaps something is amiss and the usual threats of get it done don't seem to work, management crowbars open the purse strings and highly paid consultants are often sought after to clean up the mess. Sometimes the consultant can fix the mess. Sometimes the power of management to $*#%& up a project far outstrips anyone's ability to fix it.

Jenny was lured hired to make some minor modifications to speed up a system of the latter variety that was written by an unsupervised fresh-out. It was way over budget, behind schedule, full of bugs and not feature-complete. The customers were not happy campers, and were not bashful about expressing their outrage dismay at the cost and progress. Basically, just another day in the office for Jenny.

The application itself was run-of-the-mill case management software, storing basic demographic information about clients, the people that referred them and the history of their cases. It's a SQL Server database with ASP.NET forms to interact with the data. It should have been pretty simple stuff. Except...

The design documents were literally coffee-stained napkins and Jenny was forewarned not to discard them. There had never been a DBA on the project. Naturally, this led Jenny down the rabbit hole...

One of the first things she encountered everywhere was that columns that stored Yes/No data were declared as VARCHAR(20), and stored the literal strings "Yes", "No" and a variety of things that conjured up nightmares of FILE_NOT_FOUND.

The application also had a number of data fields where the user must select a value from a pre-determined list, and the list is stored in the database. That is, there is a list of Suburbs, a list of PostCodes, a list of Illness types, etc. Rather than have tables called Suburbs, PostCodes and Illnesses, the developer opted for one table for all of them (interestingly, the booleans were not stored here, and commanded their own dedicated table). The table is called RefData_5, and whenever you want the data for any list (e.g.: the list of suburbs) you have to select from that single reference table and filter to get the records where RefType is "Suburb"). To complicate things, the table contained constant string values, numeric postal codes and enumerated values. Thus, once you queried the data, you needed to explicitly convert it to the correct type before you could use it.

Of course, prior versions of the table still existed: RefData_4, RefData_3, RefData2 and RefData_1. Unfortunately, they weren't just there as unreferenced remnants. Some of them were referenced here and there throughout the code base and stored procedures, which led to all sorts of instability as table definitions changed over time.

Adding to the woes, the code was not stored in any source control system; Jenny was handed the good copy on a USB key.

Just to make things interesting, once a constant had been identified as applying to a particular situation, the PK of that constant record was not stored in the foreign record. Instead, the constant literal and the single character key (e.g.: "12345" and "P"ostcode) were stored. Of course, you had to ensure that you never used the same key for two different data types in the REF_DATA_n table, or going the other way might become an interesting challenge.

Perhaps most impressive, is that the graduate managed to write queries on this structure. They were hundreds of lines of T-SQL if-else's and ran, well, glacially, and mostly returned the correct data. Mostly.

So now Jenny gets to explain to management why their system that was inexpensively developed by the graduate ran so slowly and inconsistently, could not simply be tweaked to make it faster, and that a full rewrite was in order; all to hear in return: you're a highly paid consultant and you can't fix it?! Why are we paying you?

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!