Another Generally Positive Review for Test-Driven Database Development

I would be a fool to think that my first book would receive only positive feedback.  This review, I think, is excellent.  I like it for three reasons:

1. My own self-interest
2. The reviewer clearly understood what he was reviewing
3. It contains actionable criticisms that will benefit a 2nd edition, if there is one

Vanity of the Database Authors

Mr. Carlson's review is, I think, generally positive.  This means that it will cause more people to buy my book, which is nice.  It also means that it fans the flames of my own vanity, which is really nice.  Most importantly, though, it means that more people are likely to actually read my book which, in turn, means more people are likely to take a sane approach to database development.

Deep Understanding

The author of the review in question clearly took the time to read, understand, and evaluate the concepts in my book.  I think a lot of people who buy books do this but, for some reason, only about half of people who write reviews appear to do it.

Mr. Carlson didn't necessarily understand all of my motivations or what I know about outside the scope of Test-Driven Database Development: Unlocking Agility but... hey... who's fault is that?  Right?

Actionable Criticisms

Not everything Mr. Carlson has to say is positive but those bits which are negative are highly actionable.  He says I give the appearance of not understanding normalization.  I do understand how to do it and why people do it in addition to why it actually should be done.  However, looking back, I can see how someone might get the impression I don't if they were basing their opinion solely on my book.  In the 2nd edition, I will remedy that by adding material that addresses those kinds of concepts.

Likewise, he makes the point that I don't address object-relational-mapping at all.  He rightly guessed that it is because ORM runs contrary the message I send - databases as instances of classes with tightly-controlled and rigorously-tested sets of exposed behaviors.  Nevertheless, his argument is that ORM is a very popular concept in designing interactions with databases is true.  If I ever get to do a second edition, which I happily think I might, and ORM is still popular, which I sadly believe it may be, then I will address it at that time.

I may address those concepts sooner, too, in the form of blog entries or articles.

Why Revision Control is Not Enough

Several people have suggested that revision control might be an alternative to creating a class of databases.  I really have no way to counter such an argument because I cannot fathom what they were thinking in the first place.

The two concepts target completely and utterly different things.  I guess that all I can really do is talk about how different they are and hope some people change their stance.

Revision control (e.g.: CVS, Subversion, or Perforce) allows you to track and manage the changes you are making to source code.  The point is to (a) provide a place of safekeeping for your documents of design and (b) to allow you to access previous versions of a design.  When you update the design of a class, you create a new version of it in source control and do not generally see older versions unless you go looking for them.

The point is to allow you to work on the most recent design in a particular branch while keeping a history of what was done and not betting on a single computer to keep your source code safe.  In short: Source control is about managing the relationship between developers and designs.

A class of databases has a totally different functions.  Like any other class, it's most interesting relationship is between it and its objects.  Yes, a database class must store the steps required to build older versions in it but for a totally different reason that why a version-control system stores older versions of source code.

Whereas a revision control system tracks versions for the benefit of the development process, a class of databases must track revisions for the benefit of the database-construction process.

Storing the incremental deltas required to get from each version to the next and forcing all databases in a class to follow the exact same path of growth enables test-driven, agile database development in two ways.  For one thing, it drastically simplifies the database deployment model; every single database of a given type grows exactly the same way.  For another, it allows you to properly test the transitions from one version to the next.

The versions of a source file are created because a developer has learned something, changed his mind, or otherwise revised design and they are created to ensure that developers can revisit previous designs.  The versions of a database class are created because a revision to design was actually released to production and they are created so that every database in a class is built in the exact same way as every other database in that class.

The Version Manager

One of the more recent phases in my journey to a class of databases was understanding that the best way to codify a database's design was as the series of revisions that could get you there rather than in terms of specific design elements.  It's harder to find fault in this way of thinking because I still use and recommend it today but it was incomplete; sufficient to enable some level of sustainable database development but not true test-driven database development.

Let me refresh your memory on the building technique to which I am referring.  You store an ordered list of scripts that are used to build a database.  You build some infrastructure that ensures only the right scripts are executed against any given database and in the right order.  So, if you have a version 2 database instance and you want to upgrade it to version 5, the infrastructure will execute the steps to upgrade to version 3, then 4, then 5.

Digging the nugget of truth out of this way of doing things is weird because the thing is its own nugget of truth and, at the same time, is not sufficient to support a modern test-driven environment.  The reason for this weirdness is that version-based builds are the best way I know to do things but they aren't enough.  More was required.

Anyway, the grain of truth in this way of thinking was the recognition that the actual revisions applied to real production databases should govern how we organize database build scripts.  That is, while I had not yet discovered the true class of database concept - at least not as I understand it today - I had discovered one of the principles that drives test-driven database development: the historical reality of important database instances must be respected and always trumps our wishes, hopes, and ideals.

The Database Installer

Another fallacious idea I, and many others, had was to treat database instances like programs that need to be installed.  Again, there are many things wrong with this line of reasoning, but something positive came from it.

You know me... "Mr. Positive."

This particular step in my journey to a class of databases bore what was, at least for me, a pretty subtle value.  Part of the subtlety came from the fact that the installer paradigm looks like it works for longer than a lot of its predecessors, which tended to break down extremely early.  Part of it was my own stubbornness - I was spending so much energy arguing the small improvement that I couldn't see the bigger improvements waiting just around the corner.

I'm pretty sure that's irony: that this way of thinking was so successful kept me from seeing other, more successful, ways of understanding a problem.  Wait.  Maybe that's not irony.  Maybe that's the human condition.  ...or maybe those things do not really oppose one another.

Anyway, grain of truth in this way of imagining database build technologies is that it recognizes the importance of discrete, tracked, testable deltas in design and highly controlled, repeatable ways of introducing those changes.  That ends up being a pretty fundamental concept.  It serves as the basis for building a testable class of databases, enabling test-driven database development, and ultimately unlocking database agility.

So there you have it.  Another step in the journey.  Another failure to hit the mark.  Another lesson that built to what we know today.

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.

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.

My Journey to a Class of Databases

If you know anything about me or you read my article on InformIT, Ten Tips for Constructing an Agile Database Development Environment that Works, you know that I think the foundation of database TDD lies in creating a class of database.  That means transferring focus from the design of individual databases onto something that makes databases.  You want the ability to create or upgrade as many instances as you like and know that they all have exactly the same design.

Exactly the same design.

There are many possible ways to implement a concept like that, though.  When I began developing these concepts nearly a decade ago, before I even knew that I wanted a class of databases, I was just focused on controlling database creation.  I experimented with a lot of different kinds of infrastructure and a bunch of different patterns of database growth.

Since I started being a professional software developer, I've tried a bunch of different ways to control how databases grow and transform.  I think a lot of people have had similar thoughts and various times in their lives.  These are the ways and times I thought about these problems.

The Fool's Errand (pre-2005)

The most naive solution is the idea that you can specify what you want the design to be right now and have some tool that will update an existing database to have the new design.  Sometimes, it is a tool that compares two databases.  Sometimes, it is a diagramming tool that will inspect a database and figure out how to make it comply with a drawing.

magic will transmit design changes!

The problem is that this doesn't work.  It doesn't work for the same reason that you can't unscramble an egg.  There is no way for a software system to look at the current design, look at a new design, and figure out how to get from point A to point B.

At least, it's not possible to do that every time and with current technology.  Maybe, one day when we have computer systems that can infer intent, it will be possible.  Right now, however, that's too complex a task for a computer.

The Installer Fallacy (2005-2006)

Another way of thinking about the problem is the way we imagine installers.  Databases have components.  Components have dependencies.  You ask the installer to make sure the features you want are there and it ensures the dependencies are satisfied.

The problem is that there is always a meltdown.  In this case, I'm using that term a little less figuratively than usual.  In a healthy database design, things are changing.  Tables are splitting and recombining into newer, better shapes all the time.

The features all melt together more quickly than you could imagine.  Pretty soon, it's difficult to tell why you are creating separate features and components at all.  Eventually, all the components blend together and you wonder why you ever divided components in the first place.

Rise of the Versions (2006-2008)

After about my third database "feature" that depended on exactly one feature, which in turn depended on only one feature, I started to get the message.  I realized that the forces in the database world are telling us to organize around time, rather than around features.

It turns out that there is usually at least one database instance that as an extremely linear path of transformation, and it happens to be the absolute most important kind of database there is: a source of record database in production.

Production databases tend to metamorphose over time in a series of discrete transitions from one design to another.  At the same time, production databases are the most indispensable and long-lived databases of all.

Everything else (e.g.: test databases or development databases) tends to have a little more flexibility.  At the very least, nothing else has less flexibility.  So why shouldn't the most important and least flexible kind of database define how all databases of a particular kind are built.

Have a Little Class (2008-present)

When I started formulating these thoughts into something that I could start evangelizing, I realized there was more to this than just regulating the flow of design changes from a development environment out into a production environment.  That's an important feature but it's just an implementation detail of a much more critical shift in mindset.

a path of confidence

What really matters is having uniformity of design between all the different database instances filling the same role.  If you have that, tests executed against one instance allow you to make predictions about how another instance will behave.

That mechanism - that way of thinking - serves as a critical underpinning for test-driven development in the database world.