Consistency in Coding (And Databases)

I've been thinking about consistency in coding, and coding standards. The thoughts I've been having on this concept have been sparked by my basic human desire to have consistency in my life, along with work I've been doing with the CRM that my company uses.

I've been doing a whole bunch of work on the database of this CRM, basically copying records across a couple dozen tables. I've noticed lots of inconsistencies and design problems in the database, and I thought I would enumerate what those problems were, and try to find the lessons that I can take from those design problems:

1. The database employs the use of natural keys, as opposed to surrogate keys. An example of a natural key is the combination of a first name, middle name, last name, and social security number to identify the uniqueness of a record. An example of a surrogate key is giving a unique number to each record, and targeting that unique number for database selects, updates, etc. I believe that employing natural keys is a poor design decision, but not everyone agrees with me on that; however, I will never ever ever build a database that relies on natural keys.

2. There are a few tables in the database that rely on surrogate keys - an unnecessary inconsistency because the table has fields in it that allow it to link to its parent table via the natural key

3. There are major inconsistencies in column naming. Some date columns are suffixed with _ISO, some abbreviate Date with "DT" and some spell "DATE" out in the column name. There are other columns that have similar naming inconsistencies, too, such as "Account" and "Acct" or "Code" versus "CD" or "Event" versus "EVT". I am always mixing these up, and all that confusion (which I'm sure occurs internally in the company that makes this CRM, as well) could have been avoided by simply making naming conventions more consistent.

4. Data Duplication. The database has datetime stamps to indicate when the record was created, and it also has an integer representation of the time it was created. I'm sure this integer representation is a carryover from legacy code and technology, but the column is still there, taking up space, and wasting resources.

5. Lack of normalization. The database has a UDF recorded value table that has about 200 columns, and at least 2/3 of those columns are always null. That table should be refactored/pivoted, which would reduce design complexity, and the amount of code required to represent that table -- not to mention memory required to store that data.

6. Too many tables with too many columns. The average table seems to have at least 75 columns (with several having more than 200)...that's a database design smell, in my mind (and nose).

There are other things too, but you get the picture.

So, why do these problems happen? In my experience working with software design, there's a number of reasons:

1. Legacy technical debt. Carryover from older technologies that didn't have some of the bells and whistles we have now are a major reason why inconsistencies occur in the software - it's usually easier to write a wrapper to interact with old technologies than it is to completely re-write them.

2. Too many people. Different people working in different areas of the product (along with no naming conventions/standards/whatever). If there isn't a set standard on how datetime columns, bit columns, recurring-theme columns are named in the database, then there's going to be inconsistencies.

3. Learning. No one knows everything, and I know less than most people; however, I'm learning all the time, and I am constantly finding things I did months or years ago that I would do different now. This happens all the time in software, and I see plenty of examples in the database where it appears this happened.

4. Urgency to the market. Let's face it, software is meaningless/worthless if it never gets out of design, construction, or testing phases. Not to mention that companies have payrolls to meet. There are always trade-offs between design excellence and need to get a product to the market. And sometimes those who control the money decide that they'd rather deal with higher support costs 3-6 months from now than higher development costs today.

5. Dysfunctional development process. When the development process doesn't support what the software is trying to do, and the team isn't "optimized" to use developers' strengths in the right way, it can lead to "cowboy programming".

What to do about it?

There's all kinds of things to do. In fact, there's people and companies who make their living off of solutions to these problems. I don't have all the answers, but here's what I do to avoid some of these problems:

1. Define a clear coding standard. You can borrow from Microsoft, online forums, or convene with other developers to make decisions on how members, attributes, methods, interfaces, classes, etc should be named. There are also plenty of online and book resources on style guidelines.

2. Keep learning. Learning things, in the short term, leads to inconsistencies, but helps push your code, database, or whatever, to be as good as it can be

3. Incremental refactoring. Rome wasn't built in a day, and neither is a 100,000 line application. Baby steps is the way to go. Slowly, but deliberately, refactor to achieve consistency.

4. Choose the right software development method. Waterfall, SCRUM, TDD, XP, some combination of all of these...Team buy-in, and a methodology that suits the skill set of your team (including your business analysts and QA people, along with developers) is important to produce quality products.

5. Pragmattic Programming. I love the book "The Pragmattic Programmer," because it outlines the type of developer we should all strive to be. I wrote a blog on "Writing Code That Writes Code". In it, I demonstrated an application that helps me produce very consistent code.

Like everything else in life, whether it be good health, a good family life, a good career, etc, there's no silver bullet. Practicing fundamentals, self-improvement, humility, and understanding that you will never be perfect is the way forward.