Twelve and a half years ago I wrote my largest hit count article of my writing career Ten Common Database Design Mistakes. It currently shows as having over 1.2 million hits, despite having a misspelling in the first paragraph. After this length of time, you would think that things would have changed and we would have found new problems to be concerned with. You, my reader, would be wrong. In fact, all 10 concepts still would be my top 10, though I might let #8 (Not using stored procedures to access data) slide a bit lower, or at least change it to “Not using relational database technology to its fullest.”

In the years since I wrote that article, I have come to believe that the first item is the major problem that continues to plague us the most here in the 20’s: “Poor design/planning.”

I have spent a great deal of my professional career designing and coding software, and much of my writing and speaking about software creation centers around this base concept that while most of us spend a lot of our time trying to come up with new coding techniques, they largely don’t matter that much. Coding techniques are the least of your problems as a programmer, understanding the problem to be solved is.

It reminds me of a programming challenge we had back when I was in college: sort algorithms. Quick sort, bubble sort, etc. Learning these algorithms was certainly very useful to teach a newbie programmer like myself the basics of programming (I still remember how to do a bubble sort,) but in 20+ years I have yet to need to program a sort in a piece of customer code. What don’t I remember from my college years? Much on how to make sure I was sorting data as the customer wanted, or if the customer even cared about sorting the data. How many times have you thought “That process works really fast, if only it did what I wanted it to?”

The Job of the Dog Food Chef

A term that is often bandied around in programming circles is “eating our own dog food”, and it is a wonderful concept. If you are in charge of mass-producing dog food for lots of animals, how do you know it is good? As the chef, you should taste it (assuming it is healthy to do so) and feed it to the dogs you know and love. If you are willing to feed it to yourself and your dogs, then you have confidence in it. This makes great sense in a company that creates software to sell. For example, Microsoft creates the Windows Operating System, SQL Server, and applications for many operating systems. So as a company, they use that software for critical operations before foisting it on others. And tying this back to the primary point of the blog, making sure it solves the problems they as a company has, and could be morphed to meet other company’s needs.

Unfortunately, one of the interesting aspects of being a computer programmer in a non-software company is that I spend a lot of time using software other people have created, but little to no time using software I actually wrote or even contributed to. In fact, it is very rare that I use anything I have designed or built professionally, because it runs parts of our company’s financial systems, reporting systems, and some parts of our websites.

As a data architect, I do my best to understand what the customer wants, but most of the time it is filtered through layers of communication and time management that get in the way. First, most customers don’t really understand their own problems, especially the minutiae of what they want. Second, the people capturing requirements rarely know enough about the customer to know if the customers do understand their problem. I know my architect job is primarily to interpret requirements given and create (or get created) software to meet the requirements. Even this process is fraught with issues for many architects because either they misinterpret the requirements, or in some cases, just take shortcuts on the way to a solution.

If I was a chef of any kind preparing food that is applicable to humans, I will only rarely ever meet those ingesting the food I have created. I do know these folks will complain about the quality at times, because I complain about the quality of software I use often. Why didn’t the programmer think of this? Who did it this way? What the heck? All of this leads back to the fact that many programmers don’t actually have a chance to use the software they have created for real. Even if it was 100% quality tested, and did exactly what it was created to do, the true users find that it tastes funny.

The Micro-example that Inspired this Blog

About 3 years ago, I started a Twitter account for posting Disney Parks pictures. To manage my pictures, I basically did what any other person would do, I created a set of folders for pictures to tweet, and pictures that I had tweeted. This worked wonderfully for a few weeks, but it quickly became tedious trying to remember what I had recently posted.

So, I broke down and built a database. My first pass at the database was simplistic. Because I don’t like writing UI code (and haven’t in 10 years, at least), I built my database for working through Management Studio as my UI. This meant I kept it simple, just solving the one problem on my mind I thought I could solve easily and didn’t really take the time to do a design or consider what all my software could do for me eventually.

As I started to use the software to manage my process, I realized something. What I had built was ok, but I had really done a mediocre job. I had tested my code, but some stuff I had built just didn’t work as the months passed. I had built randomization functions that weren’t very random. I didn’t build in picture management. I had used natural keys in places (for easy editing), but this meant that making changes to the keys was a lot of more work than had I used surrogate key values.

In reality, I had created some blech dog food and this is what I had to eat. What happened next was exactly what I have seen other users go through. I made things work… sort of. I didn’t always use the software, I repurposed columns, and I was only getting half of the value I wanted out of the software. What I wanted was a tool that would take the work out of picking things to tweet about, leaving me to just take pictures, classify pictures, and write 240 characters a day about them. Instead, managing the software was taking more time than the creative process, which was certainly not my goal.

Pretty much exactly what I had seen out of software users for all these years. Managing the software that manages corporate data needs to be almost imperceptible to the customer. A natural part of the process. Going back to the dog food example, in a perfect recipe, the nutritional value of food should just happen. I eat a delicious meal, and I get all of the vitamins and minerals my body needs.

I Then Did What Most Programmers Can’t Do (but really ought to)

I went back to the drawing board and captured my actual requirements. I did a data model, created the software I wanted, threw out history, and started over. My model isn’t large, only about 16 tables (it and the code is here in Github still in not extremely documented fashion), so it isn’t an extremely complex build. But I made sure I did (or could) meet all of the requirements I have in mind for the upcoming future.

It also helped that the entire team consisted of one person, who took the time to think of themselves as multiple people. In my first build, I catered to the user’s fear of having to edit normalized tables using surrogate key values for references. I haven’t posted this blog yet, but my next blog will discuss how I got around this limitation (and it doesn’t actually use stored procedures)!

When gathering my requirements, I put myself into the eyes of the hungry dog, who just wanted to eat a bowl of something delicious that made their fur shiny; not the person who would be building the tools to cook the food daily. I knew what I wanted to be able to do and put that down.

While most programmers can’t just dump the software someone wrote (particularly bad when it was your own code!) and start over, they can spend a little time when building software putting themselves into the mind of the user thinking “If I had to use this process to do my job, is there a way I can make it better for them?” Not always does this mean a polished user interface. While I would love a polished interface for my picture selecting process, had I built a UI on the first version, it still would not have solved my problem. Right now, the process just produces a set of SQL statements that I have to edit the content of and then execute.

I am happy with that because those statements stop me from making all sorts of dumb errors; leaving me the freedom to be creative in the process. That was the problem I needed to solve and the technology just makes it happen.

Summary

In the end, the important goal for any software project is solving the problem that someone needs a solution to. No matter how well written, high performing, and beautiful looking your software, it’s all meaningless if your solution doesn’t do what your customer needs.