Revisiting Decisions: Improving Systems and Processes

Revisiting your code of the past is a worthwhile exercise. More specifically, revisiting the decisions, reasons, and other factors that led to the current state is an extremely valuable exercise. Authors of technical work (databases, applications, calculations, etc) should re-evaluate the purpose of our systems and decide when "the way it's always been" needs to become "the way it used to be." While it is easy to proclaim this, it is often difficult to do in practice. Why? Because it's complicated, of course! 🙄 The more serious answer is that humans, including yourself, are involved. Technical people like to focus on the technology even though the technology is often a side-story to the bigger picture.

This post tells a story of a single project that spanned over more than a decade. While there are technical elements involved, you'll see some of the biggest technical issues were human created.

A non-exhaustive list of human elements include:

• Who was involved? (developer, end-user, other stakeholders, etc.)
• Experience of the people involved.
• Internal politics, then and now.
• Process and systems evolution.

The Widgets database

This story you are about to read is true. The names have been changed to protect the innocent. For this post, we will refer to the project as "The Widgets Database." It started with an Excel file that had far exceeded what should be done in Excel. The top of the main Widgets worksheet had a line with the warning:

Do not sort, you will break formulas. Spoiler alert! The formulas were already broken.

Initial Planning

The year was 2012. It had been determined I would help build The Widgets Database. There was a defined Calculation that needed to be performed accurately at scale. The Calculation was not supported by the corporate-level information system and wouldn't be supported for at least 3 years. We had obviously outgrown the Excel based solution. The Calculation was necessary, grounded in well-documented legal details so it was an obvious priority. The legacy process at the time used physical paper forms shuffled between humans and multiple physical locations, and data was manually fed into an Excel file. This Excel file featured one main worksheet with hundreds of columns. Dozens of the columns had complex formulas, with many of the formulas nested on top of multiple other formulas as part of their calculation.

A simple fact was that regardless of the warning to avoid breaking formulas, many of the formulas were already broken and had been for a long time.

Internal Politics and Technology

The plan for The Widget Database was formalized, we would update the internal process to use digital forms and store the data in a proper relational database. MS SQL Server was already in-use throughout the org and was an obvious choice for the backend. Unfortunately, this project happened to have the word "form" in it which meant it was wrapped into the organization's much larger "e-Forms Initiative."

cue ominous music 🎵

Being branded as part of the e-Forms initiative meant a specific technical platform for building forms was predetermined and pushed on the project. The InfoPath platform was an eForm product targeted for Sharepoint, and because The Widgets Database needed forms, those forms must be built with "the approved technology."

What's the catch, you ask? InfoPath didn't talk to MS SQL Server. At all. We re-evaluated if SharePoint could handle the backend needs and that was a resounding "No." MS SQL Server was the appropriate backend, InfoPath was the mandated frontend. To overcome the minor hurdle disaster of the frontend not talking to the backend, external consultants were brought in to build a glue layer in C# that... gestures wildly. There was a lot of discussion and swearing about XML, SOAP, and Kerberos while building this over-complicated interface.

InfoPath also didn't provide any reporting, so I built that portion quickly in MS Access.

Design Implications

The complexities of setting up communication between frontend forms and backend database resulted in a plethora of limitations. One of the biggest limitations was the form could only send the full form's data in XML format that provided no detail of what the user changed in the form's data. This led me to design a convoluted interface in SQL to handle all of the business logic within stored procedures. All data modifications were routed through a single stored procedure (sproc) named dbo.controller. This was a bad decision. I knew it. The consultants knew it. However, given our list of choices it was the best option we had.

This monster dbo.controller sproc had logic that would:

• Determine the action being performed based on the data passed in.
• Coordinate data validation sprocs (action dependent).
• Run DML sprocs to INSERT/UPDATE/soft-DELETE records.
• Log errors (unless there was an error that rolls back the transaction, then inner detail was lost, but that wouldn't happen, right?).

The good news was the project launched on time in early 2013, early enough to perform "the calculation" for production purposes in Summer 2013. The results were accurate, and it certainly was better than the legacy Excel file populated from paper. The users and business units rejoiced!

Surprise!

Fast forward less than one year and Microsoft announced InfoPath was deprecated and would be EOL (end-of-life) after Sharepoint 2013 was retired. While it gave a long runway to exit, it also meant Microsoft would not be fixing the complaints we had with the platform. The other reality was it was prohibitively expensive (💸) to maintain the C# -> Kerberos -> SQL bridge for even the most trivial application maintenance.

This left us needing to rebuild the front-end interface. The timeline given was short and we had other, more interesting projects to work on. It was decided to quickly build forms into MS Access where I had built the initial reporting. MS Access natively talks to MS SQL Server and we could quickly rewrite the minimal glue logic in VBA, and get back on with life. It was quick, easy, and actually worked better then v1.0 did. The users and business units (mildly) rejoiced.

The project worked well enough in this state from 2014 - 2022 with minimal maintenance. It was easy enough to tack on new requirements to "the calculation" and, overall, served the purpose it was built for.

Technical Debt

We had lingering technical debt in the massive dbo.controller sproc. There were piles of T-SQL code that had to decide when to add rows and when to update rows in a specific table key to reporting. The sproc logic made the correct decision the vast majority of the time. Unfortunately, vast_majority <> 100%. The sproc made decisions based on 10+ variables, spread across multiple stored procedures that dbo.controller called. Fixing one side effect spawned a new side effect of its own. The documented side effects turned out to be easier to document, manage, and fix than it was to fix the source of the problems. Fixing the root cause had been on the wish list for years, but was never a high enough priority because it would involve taking the system down to the base tables and rebuilding.

Another relevant factor in the decision to not rebuilt was in the departments involved with The Widget Database, there was zero human-related turnover for nearly a decade. The person that worked the front-end process was hired as the system was going live, and I had been involved from the beginning. We had muscle memory in how the system operated, and had learned a variety of tricks to avoid the worst of the oddities.

Out with Legacy, In with Automated

When the front-end position turned over in 2023, I proudly shared the documentation we had accumulated with the new person and scheduled a time for training. The documentation included a user guide, dozens of pages long, with screenshots outlining various common scenarios. We quickly come to learn the manual only documented some of the steps required to avoid the edge case problems. It had been years since I had discussed those so I could only provide vague guidance.

With turnover came a huge increase in maintenance costs! Naturally, I promised to fix it.

After working with the system for a few months, the new person responsible for the front-end work asked a key question: "Why do we use this process instead of using this new data source over here?" The question had been asked and shut down over the years by various parties, and historically the data steward would defend its necessity. This new person later admitted to me they were afraid I would shut down the conversation since the project was "my baby" as the sole original project member. Instead, I was thrilled with the prospect of jettisoning this pile of technical debt.

The question was asked in August 2024, and the legacy Widgets Database is retired as of July 2025. The new process replacing it relies on the corporate level system for data management and the Calculation. The internal reporting is supported via an automated ELT (Extract, Load, Transform) process to ingest two data sources into the on-prem SQL database for internal reporting and QA purposes. The person maintaining the new process quickly learned the basics of SQL querying to provide the reporting they need and are exploring new ways they can leverage the reporting instead of spending time maintaining the legacy process.

Summary

The Widgets Database was used for a total of 12 years. The database itself persists with new structures, but is no longer the same entity it was previously. We no longer maintain a custom GUI, forms, or user interface of any sort. Instead, users simply use SQL to access the data for reporting directly. The only business logic in the database exists in the ELT code for QA purposes and the views used for reporting.

Personally, this project provided me a wealth of learning through the challenges we had to power through. I have often wondered how the system would have operated and performed if we had been able to choose an appropriate frontend technology at the beginning. We would not have had to spend money on consultants for the initial glue layer, and the dbo.controller sproc would never have existed.

On the flip side, if it had been built in a more maintainable fashion, would I have been willing to see "there is a better way?" Or maybe, would it have evolved more naturally into a different, also better way?

Hypothetical scenarios aside:

The users and business units rejoiced!

Need help with your PostgreSQL servers or databases? Contact us to start the conversation!