// I need to

ModernizeUpgrade without breaking it RescueFix what someone else started Build NewTurn an idea into a product Reach More PeopleGet found, get used, include everyone

// Company

Case StudiesExpertiseAbout + CareersResourcesContact Book a call →

Architectural History and the Value of Maintaining a Clear, Consistent Database

Why do the stained glass windows of an old Pasadena church suddenly turn to concrete? A lesson in keeping a clear, consistent database and its history.

Art+Logic Mar 7, 20173 min read

From the Art+Logic archive · written in 2017. The tools may have moved on; the thinking still holds.

Image of church window by Jonathan Gonzalez

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.

keep going

More to explore in Modernize.

All Modernize resources →
let's talk

Got a problem like this one?

Tell us what's stuck. A real person replies within one business day.

Talk to our team →

Talk to people who know software, not a sales script.

Your first call is with Aja, our VP of Sales. He'll help make sure you're solving the right problem. Here's what you'll cover together:

  1. What you're trying to accomplish

    The business goal first, then the technology.

  2. Possible approaches

    And the tradeoffs of each, in plain language.

  3. Risks you may not have considered

    Technical and business, before they cost you.

  4. Whether AI changes the math

    What's practical, and what's just hype for your project.

  5. Sensible next steps

    Even if the right next step is not us.

book-a-call.ics

Loading the scheduler…

Open the booking page →

Some problems are easier to explain in writing. Send us a few lines about what you're working on, and we'll reply within one business day. Helpful to include:

  1. What the software does, or should do
  2. What's stuck, or what's worrying you
  3. Any deadline or budget you have in mind
note.md

Loading the form…