Technical Debt, Database Design and the Days of Reckoning

Technical DebtNo one who has been around this planet for more than a few decades is unaware of the saying, “You can pay me now, or you can pay me later.” And yet, almost universally, when someone is presented with the choice of paying now or paying later, they invariably choose to pay later. There are a variety of reasons, ranging from legitimate to cynical, why we typically choose to pay later. Reasons range from not having the proper justification or budget to make the payment, to the cynical premise that we probably won’t be here later when someone else has to do the paying—if we’re lucky. That deferral comes in many forms. No matter what the reason, in the systems world, when you defer the payment, you end up with what is called “Technical Debt.”

Ward Cunningham defines the term “technical debt” as the eventual consequences of any system design, software architecture or software development within the codebase. Much has been written about technical debt, and the fact that technical debt must eventually be paid. Since so much has been written about it, I’m not going to explore the theoretical reasons why that debt must be paid (trust me, it must be paid). Instead, I am going to point out some real-world examples of technical debt, specifically in the context of database design, that you would really be glad to not have to pay…if you have a choice. I do this in the hopes of helping you to avoid finding yourself in the same position someday.

In my career over 30+ years as a Program Manager and Project Manager in the Fortune 2000, both domestically and internationally, I have frequently been called in to take over a project or program that have been started or completed by another team under a past “administration”. I have repeatedly been asked to assess the stated problems, and justify the design and implementation of a project to somehow “save the codebase.” In the beginning, the problems are always stated by executives in narrative form:

  • “Performance has ground to a halt—it takes one minute to look up a customer!”
  • “We can’t add any more records into the database.”
  • “The nightly batch cycle will cut into the daily online window soon at the rate it’s slowing down.”
  • “It’s taking two hours for this report to process 10,000 records. That can’t be right, can it?”
  • “The total of this report should cross foot to the total of the other report, but it doesn’t. Both reports look correct on their own, and yet they don’t match.”
  • “When the Vendor database crashed, it took five other completely unrelated systems down with it. I didn’t see that coming.”
  • “The system works great in Illinois but we can’t handle scaling demands in New York. I guessed we should’ve figured that if it worked in Illinois, we’d want to do it in other states.”
  • “This system used to let you delete an incorrect payment transaction, but now, suddenly and without reason, when it deletes the transaction, it deletes the vendor at the same time!”
  • “When we bounced the server, ‘online’ went down for 10 hours!”

If you’re reading this blog, you’ve likely personally seen some of these situations by now, right? But let’s flash forward…When you see a lot of examples of failed systems over the decades, you begin to see patterns. Many of the patterns I’ve seen come from accumulated technical debt in Systems Architecture, particularly in database design, whose time has come to be paid. Wouldn’t it have been great if it had been paid upfront? Somebody asked me once what I thought about Systems Architecture. To quote Gandhi, “I think it’s a great idea!” But if the architecture is poor/weak, you’ve got debt to pay.

My experience has shown that database design and performance technical debt emanates from five basic categories of investment deferral of proper Analysis and Architectural Design activities:

  1. Missing/incorrect/insufficiently detailed user stories and use cases
  2. Missing/incorrect logical data model.
  3. Missing/incorrect/poorly normalized physical database schema
  4. Missing/insufficient metadata about table columns
  5. Missing/insufficient non-functional specifications captured

You wouldn’t believe how many times I have seen examples where certain architectural documents, tools or processes are completely missing in a particular shop. Consider the following conversational snippets I’ve had over the years:

  1. Q: “Where are the Use Cases?” A: “We don’t ‘do’ Use Cases-we’re Agile.”
  2. Q: “Where are the non-functional requirements?” A: “We just use the same ones over and over—if I can find them, I’ll send them to you.”
  3. Q: “Can I see the Database Design?” A: “Our back-end guy quit before the design was started, so we coded up what we knew from sitting in on the meetings to stay on schedule—after all, everyone here knows SQL.”
  4. Q: “Who’s handling Release Management?” A: “Everyone is responsible for releasing their own code when they think it’s ready.”
  5. Q: “What tool are you using for code management?” A: “We’ve done just fine without spending money on those overblown code management suites.”
  6. Q: “How are you handling regression testing?” A: “We keep all of our test cases in spreadsheets and manually run which ones we want when we think we need to.”
  7. Q: “What kind of testing do you do?” A: “Happy path-that’s all we have time for because the developers always run late.”

All these examples point directly or indirectly to deferred investment decisions, or a lack of organizational expertise, that have incurred technical debt which will manifest itself in a poor database design and implementation—debt that will someday require payment. Are you ready to file for early retirement yet? Or are you in the position to have to pay back the debt now?

Fortunately, every one of these seemingly intractable problems leaves a forensic trail of breadcrumbs that can be traced back to its root cause. For every deferred investment decision that you or your predecessors have made in the past, there is an optimal strategy and range of reasonable tactics for recovery (aka, technical debt repayment) going forward, particularly in the database design space. As bad as the examples sound above, there are very, very few examples of problems that are so severe that the entire code base has to be trashed and started over. However, just because you know the root cause, and just because you have a working strategy with a range of acceptable tactics, that does not render the best technical solution to the problem. It merely begins focusing the point of attack.

Assuming you have found the point of attack (a challenge in itself), you’ll need superior design expertise to work your way out of these issues. Not “good” design expertise, not journeyman level expertise, not even “The candidate has ten years of database design under his belt” experience. We’re talking about the best of the best—a Master level database design engineer (MCM) with the appropriate support resources from a bona fide Microsoft Certified Partner Engineering firm (not a staff augmentation firm). A Master designer is a master precisely because they have been trained to design and implement databases in the manner that takes the most advantage of both design and the platform to realize the needs of the business. They’ll know Design as Engineering Science, and they’ll know the SQL platform like few others. Because once they determine the design fixes, they will also know how to use all the inherent power of the database platform to implement the design.

In summary, I hope that by pointing out some examples of how technical debt can have negative impacts on your database design, and suggesting the proper resources to solve the resulting issues, you can begin to think about how to head off the ultimate consequences of past decisions by utilizing the resources that are available to you.

Please share this

Leave a Reply

Related Articles

A toy machine and a microphone on a table Description automatically generated

The Right Tool for the Right Job

I’m looking forward to 2024. From many perspectives, I think we are going to see great advancements, including in technology, that on one hand will make our lives easier, but also make our decisions a bit harder.

Read More »