Sometimes, old buildings have really interesting stories to tell if you have just a few additional bits of knowledge about them. Not that long ago, I found myself in an old church in Pasadena, California. It had been built in the style of European Renaissance cathedrals with lots of pointed arches, a high-vaulted ceiling, and lots of lovely stained glass windows. Those on the south side of the building would take on a beautiful luminescence whenever the late-morning sun would break through the usual LA gray haze.
Interesting bit about those stained glass windows, though; as they march from the front of the building to the back, suddenly, near the end of the structure, they suddenly stop being effervescent, glowing yellows, blues, reds, and greens, and become dull, uninteresting gray concrete. It’s hard to see why the builders would suddenly give up on one of the most interesting and beautiful features of the architecture. Until, that is, you learn one of those interesting bits of knowledge. It seems that the building was constructed in the 1920s, the roaring era of prosperity – albeit ever so shallow – when money flowed freely and everything was possible. In this climate, nothing seemed too good to adorn this building with. And then, 1929 happened. In that new climate, suddenly concrete seemed just fine. The story of the stained glass windows doesn’t make sense unless you know the story of what was going on in the broader economic life of the nation and the world.
Databases can be like that. Sometimes, an application calls for a certain set of features and a project is created to satisfy those requirements, along with a database. Then, some time goes by, something changes in the larger life of a company, and now the database that was constructed for an earlier reality is now unsuitable. So the app development team gets back to work on it and makes a few modifications here, a few more tables here – oops, that column isn’t big enough anymore, let’s expand it – and hopefully, the application is now able to handle the new requirements.
Suppose, further, that this cycle continues, not just once or twice, but many times over. It’s very easy for small inconsistencies to grow into larger ones. There’s now an awful lot of gray concrete sitting right next to some lovely stained glass, metaphorically speaking, and now it’s hard to tell why certain design decisions were made way back when this database was first called to life out of the electronic ether. Without those little, interesting bits of knowledge, it’s hard to see why, for instance, there appear to be two different stored procedures that serve the same purpose, or why there are three different naming conventions applied to the tables, or why part of the database is well normalized and parts of it are really just glorified flat files.
Maintaining and supporting a database in this situation can get much, much harder than supporting a database that is clear in purpose and consistent in style. That’s why we have a database design review process that is intended to identify and rectify issues in an application database before they get so far out of hand that it’s budgetarily unfeasible to correct them. When we hand over an application database to one of our clients, we want to be confident that what we’re providing is clean, clear, consistent, and logical.
Here are some of the areas that we review when we’re doing some QC and TLC on one of our DBs:
Style and naming – Object names in a database — whether tables, functions, views, stored procedures or other, ought to be consistent and informative. We want to strike a good balance between verbosity and clarity so that people who come to the database after us can get up to speed on it as quickly as possible.
SQL coding style – We want to see SQL code that is readable, consistently formatted, and easy to maintain and fix. We can help you identify coding practices that are slowing you down and get things moving smoothly.
Table structure – Your database ought to be well normalized with appropriate data-type selection, good data integrity, and relational integrity enforcement, to stop garbage from getting into your database in the first place.
Indexing – Unfortunately, when database performance problems are encountered there’s a tendency to start adding indexes in a somewhat haphazard fashion. Working with a good database administrator can help you figure out where you’re over indexed or missing indexes, and get rid of the ones that are only slowing you down.
