The Database Design Applicator

Another step in my journey to a class of databases was believing that a design tool could properly maintain all my database instances for me.  Again, I want to try and dig the nugget of truth out of this belief rather than beat it to death for what is wrong about it.

I think that, in this case, the nugget of truth is actually pretty self-evident.  The idea is to have a document that specifies the current design of a database and a tool that can update any database to have that design.  If you scrape away the part of that sentence that is obviously magical thinking, you are left with this:

"The idea is to have a document that specifies the current design of a database."

The part that is not quite as readily-apparent is that the motivation runs just a little bit deeper than that.  The real drive behind a tool that can update any database to a well-understood definition of the most recent design is that you have a document that codifies the current design and a tool that enforces the current design.

That's a noble goal, actually and an attainable one at that.  Think about it...

Disregarding all the ancient ideas, don't we still have a way of doing that when defining a "regular" object model?  We just do it a different way.  Instead of technical specifications and UML diagrams, we have unit tests.  Instead of a tool that generates code from high-level specifications, we have test runners.

Given a class of databases, using unit tests as the primary specification of design is a discipline that transfers straight over to database development; practically unmodified.

So that's the positive takeaway from the era of magical design-application tool mysticism: that we do in fact need a way to specify and enforce the current design of a class of databases and that, like Java, .Net, or C++ classes, that should be automated unit tests specifying how a database behaves and enforcing that specification on a regular basis.

Wrangling Wild Databases

I've received some interesting questions lately.  One question at my most recent talk for the DAMA group in Portland rang very familiar.  It got me thinking.

There are a lot of database instances out there that were developed before my book was published.  Even if everyone adopted the techniques therein, that would still leave trillions of rows of data in databases that were not in accordance with the book.  Among other things, these databases are generally created with insufficient automated test coverage so, in the spirit of Working Effectively with Legacy Code, I call such databases "legacy databases."

I cover legacy databases a little bit in the book but there is a limited number of scenarios.  This blog seems like a natural place to start addressing other issues.

The Scenario

Imagine the following:

You work for a company that sells an enterprise product; we'll call that product "Calm Cheddar."  As with most enterprise software products, Calm Cheddar has a database back end.  Calm Cheddar has been successful in several applicable markets and has been sold to numerous customers over the course of several years.  In that same time, it has grown as a product.  Along with the overall design of Calm Cheddar, the design of its database has grown and morphed over time.

manual deployment and broad customer base leads to
deviation in database builds

The final result is that there is an array of customers with varying versions of the software and each with a different path to their current deployment.  The saving grace is that Calm Cheddar's customers tend to upgrade to the latest version.  They may or may not upgrade often, but then never upgrade to something that is already outdated.

Now let's say you want to start emerging a class of databases in this scenario.  What I've shown in the past and what I teach in the first two thirds of my book do not cover scenarios like this.  This specific scenario is not covered anywhere and I imagine that there are several people in a similar situation.

The Ideal and the Real

The ideal class of databases is expressed as a linear sequence of versions created using a linear series of revisions.  The class of databases is simple, knows how to perform any reasonable upgrade, and is very robust.

Circumstances, however, are rarely ideal.

just because you want it, doesn't make it so

There are at least two lessons from Test-Driven Database Development: Unlocking Agility that apply to this scenario.  First: drive variation out of your database build process as much as you possibly can.  Second: above all else, make your database build process reflect the actual transitions that have really been applied to real databases.

These two forces might appear to be contradictory but, in fact, they align perfectly.  You should drive all variation out of your database development process, yes, but that does not mean you will succeed or will have started autonomating your database build mechanism at the very beginning of your very first database's life.  That you should drive all variation from a system does not mean that none will ever be there.

The Reckoning

The key to reconciling these two forces - the impulse to minimize variation in database build paths and the need to recognize the true database upgrade steps that have actually occurred - with the reality that there is a large, diverse population of databases within a given class is in understanding that you must merge these disparate paths together.  What was once a vast array of trickling creeks should, over time, be coalesced into a single coursing river of features.

there can be only one

Two techniques must be applied to resolve any differences between the various deployment paths.  One is taming a legacy database.  The other is remediation deviations.

Without getting into the details, the former amounts to creating a new class of databases that has conditional build logic in its very first version to address the potential of a database that has been built prior to the class's creation.  The latter consists of documenting variations in a database's historical construction patterns, then using transition tests to drive conditional logic that reconciles the various "flavors" of a database design.

Sometimes it will be a great deal of time.  Imagine that eighty percent of your database instances are almost exactly alike, fifteen percent fall into a few other distinct categories, and the remainder are "lone wolves" with highly deviant paths.

In such a case, you might want to phase your database wrangling activities, starting with the large body of highly similar databases first, moving on to the smaller groups second, and start picking off the lone wolf types on an "as-needed" basis.

This technique works great in a relatively controlled environment where, among other things, all of the databases are roughly the same version.  They don't need to all have exactly the same design to start but it does make things a lot easier if they have approximately the same design.

In the Calm Chowder scenario, however, we don't have the luxury.  Remember: we have variation in both the version and the manner of construction.

The Conditioner

A friend of mind, Seth McCarthy, has also come up with an interesting twist on this way of doing things; one that, I think, addresses the extra kind of variation.

He suggested adding a proxy over the simple, linear-step-oriented database builder at the heart of a class of databases.  This proxy's job is twofold.  First and foremost, it detects the version of a database's design then conditions it to look as though the infrastructure for a class of databases has been used to build it.  Typically, that means creating and populating some kind of version registry table.

Naturally, if acting on an empty database, the proxy does nothing but delegate to the core database builder.

rectify...

Also, the conditioner code is in a position to perform conditional transformations before delegating to the core class of databases.  It can even inject custom transitions in between steps if necessary.

The conditioner represents almost the exact opposite of the linear sequence of database upgrade steps I ordinarily recommend but special circumstances demand special responses.  Ordinarily, one would be adding a new version to a class of databases on a very regular basis and one would have to manage an ever-growing number of possible historical versions as a starting point.  There should be very little variation between one instance and another of any given version.  In that case, having special transformations that go from each version to each other version adds complexity and work.

In this scenario, however, the conditioner proxy exists in the exact opposite context.  There's only one target version with which the proxy is principally concerned.  There are many source versions and there is the potential for some amount of variation between two instances of any given historical version.  So having a special place to handle one or more of the special cases makes perfect sense.

So There You Have It

At a very high level, this should serve as a strategy one could apply to the problem of handling a large, diverse population of databases in various historical states and previously managed with an at least somewhat unreliable process such has being built by hand.

The strategy can be stated simply, though it is not always easy to do.  Capture the historical versions of your class of databases.  Codify those versions, either as conditional logic in the initial version of a simple linear sequence of modification scripts or as a proxy to a similarly sustainable database class format.  Drive each conditional behavior from transition tests that model the variant starting points.  Force all of the variation out of existence in a controlled way.  If you have too much variation to handle all at once, ingest smaller segments of the source database space into your class of databases.

Tracking Versions in your Database

As you may or may not know, I preach creating classes of databases over manipulating the designs of individual databases.  There are many different implementation decisions one could make while elevating design from instance to class.

In a previous post, I discussed the shape of the class itself, arguing that it should be organized around actual released versions plus the thing you intend to deploy next.  This is not revolutionary.  It's not ubiquitous but I'm certainly not the only one to suggest that this is how databases should be maintained.

There are several ways to manage how versions are tracked when an instance of a database has been deployed.  One way that I've seen done is to keep a text file with a list of the scripts that have been executed.  Another way is to fill a directory with scripts that have been executed, with each script living in its own file.

The way that I think works best and that fits most naturally within the concept of a class of databases is to store the state of a database instance within the instance itself.  The alignment is so strong, in fact, that it makes the other methods seem bizarre and beyond the realm of consideration.

Dependency

From a practical perspective, storing a database's version information inside the database only makes sense.  You don't want to have something outside a database that is required to run or maintain it.  What if that thing gets lost?

the puzzle stops working when
it has a missing piece

Sure, one could make the same argument about the class of databases itself but the odds of a document or set of documents that serve as the source of record for something's design getting lost are extremely low - we have revision control and backup policies to ensure that.

In most environments, the rigorous database maintenance procedures ensure that losing data stored inside a database is many, many, many times less likely than losing a text file or a folder full of scripts.  The main way that you might lose said data is if you somehow lost the database itself, along with all its backups, in which case you would probably not care if you also lost the version data for that database.

Management

Another matter of pragmatism is managing databases is that of managing the dependency between them and their version-tracking data.  If those data are external to a database instance, then you have to know where they are stored in addition to how to connect to a database.

The most dreadful consequence of this management hassle is that you might accidentally upgrade one database based on the state of another database.  Depending on what precautions you take, that could have consequences ranging from a few minutes of aggravation to loss of valuable data. 

do you want to feel like this guy?

Moreover, there is a persistent and non-trivial management cost.  Everything you do with a database - every single instance you create - also has to have this manifest of its upgrades stored somewhere else and the association between the two must be tracked for as long as the database lives.

What about all those little databases that only last a few seconds or a few minutes?  As your test suite is executing?  What about all the development databases?  It's not like tracking each of these relationships is or expensive but all those little tasks accumulate to produce an awkward development environment fraught with menial tasks that draw your attention from the things that matter.

Principle

There are several different supporting arguments and each, to me, is sufficient but all are derivatives of one overriding factor: objects are only objects if they are whole.  In a normal object-oriented environment, access to one object is achieved with a single reference.  One pointer to a spot in memory.  One connection string to a database instance.  Those are a good ways to access an object.

this is not a house
not yet

Imagine if, instead, you needed two things to make an object work.  What if a string object stored all its data intrinsically except for its length and, in order to properly use it, you had to track its length in another variable?  Could you do it?  Of course.  Would it be insanely complex?  Definitely.

The wholeness of an object is what grants it its identity; what elevates it from being a mere blob of data that can be accessed by a program into an object.  Tracking the upgrade state of a database in said database is part of keeping it whole.

Why the Linear Chain of Deltas Is the Best Way to Define a Database

There are several ways to organize the revisions in a class of databases.  In fact, there are at least two distinct ways that one's strategy can vary: how one organizes the outcomes of applying a class of databases and how one organizes the implementation of a class of databases.  It is my stance that, in almost every case, the most effective solution is to have a class of databases codify a series of released versions and to use a sequence of delta scripts to get there.

What

The first order of business is to decide what a class of databases describes.  You could have it describe the components of a database.  You could have it describe the current state of a database only.  You could have it describe each released version of a database plus the one you are working on now.

As stated above, I think the latter is best.  Rather than disprove its competitors and every other possible competitor, I will demonstrate its superiority.

There is at least one database of consequence for most release products.  Usually, that is a production database acting as the source of record for one or more software applications.  Usually, if you were to take a time-lapsed video of that database's design diagram over, say, a decade condensed down to a few minutes, the image in the video would remain almost completely static for many seconds, then it change almost instantaneously to a new design before going back to being stable again.  This process would probably repeat for the course of the video.

The real database - the most important one in your product, organization, or design - is almost always expressed as a series of discrete versions.  Its content grows gradually over time but it's design alternates between long periods of stasis and short periods of violent change.

Databases Naturally Transition from Version to Version

These long, stable versions of your production database are the natural targets around which to organize your class of databases.  Why build anything that won't produce one of those versions?  Of course, the version you are working on now is a bit of a moving target but, when it gets released, it ceases to be the version you are working on now and becomes another in the series of versions in a deployed database.

How

So I've shown that the allowable targets of a class of databases should be past and future versions of a released database only because the reality of the most important databases is that they transition from one version to another.  Once you are doing that there are a few options for how you do it.

Define Every Possible Transition

One option is to manage every possible transition in design.  That is, if you've developed three versions of your database design already and you want to add a fourth, you would produce and test a script for how to get from each of those previous versions to the latest.

Aside from the fact that this strategy cannot be depicted without violating the cardinal rule of diagramming design (no crossing lines), this is a lot of work.  You've got to test four paths for your fourth version, five paths for your fifth version, six paths for your sixth... you get the idea.

There's another option: only define the transition from the most recently-released version of your database to the version you intend to release next.  That way, when you want to add a ninety-fifth version, you only need to add to your class and test one set of transition scripts, not ninety-five.

Only Define the Next Transition

How do you get from an older version to the latest?  By applying each of the intervening transitions in the correct order.  Of course, to ensure this is done consistently, you have to build a little bit of infrastructure but that infrastructure is a one time investment and costs almost nothing to write in the first place.  It usually pays for itself in months, not years.

On top of all that, I could make the same argument for how a class of database grows as I did for what its outputs can be.

The most important, valuable, and rigid database in your life probably transitions from to design to design by a sequence of transformations applied to it.  The linear nature of its development is nothing more than a reflection of the linear nature of time (as we are able to interact with it).  You made a series of changes in a particular order so there is a series of changes to applied in a particular order.

Do It

Unless you are in the two percent of people for whom this way of doing things actually doesn't make sense, you should start managing your database designs this way: a linear chain of deltas allowing you to move between discrete versions of design and managed by a lightweight infrastructure.

I'll Be Speaking At DAMA Iowa In May, 2014

This is just a quick note that I will be speaking at the Iowa chapter of DAMA in May of 2014.  I'll announce the official date and place when they have been set.