Max Guernsey, III, author of Test-Driven Database Development: Unlocking Agility , shares ten fundamental prescriptions and proscriptions to help you be more successful in your database development endeavors.

Find out more about Max Guernsey on Google+.



Notwithstanding the self- and paternally-taught part of my life as a programmer, I basically started my software career working with databases. At the time, it wasn’t something everyone did. Straddling the programming and database development worlds for my entire professional life gave me an uncommon insight that has served me well to this day.

I was a fairly early adopter of test-driven development and of agility so, for nearly a decade before I wrote this article, I wrestled with how to get databases to work in these models. The biggest challenge with implementing agility is that you cannot possibly have a truly agile environment without test-driven development, so that’s where my focus was applied.

Around 2005, I finally started to break the problem. A few years later, I had a system that works. Is it perfect? Of course not. Does it work? Definitely.

In this article, I offer ten things that I’ve determined are necessary and sufficient for building databases in a modern, test-driven manner that makes them almost as malleable as well-crafted application code.

#1: Build Classes, Not Individual Databases

The word “the” seems so innocuous and, most of the time it is. However, when placed just in front of the word “database,” it becomes a potent toxin, corroding the minds of all exposed. Avoid the phrase “the database” and any of its kin.

If you are working on the production database and you need to check the test database to see why you couldn’t find an error, then you’ve succumbed to the poison.

Why is it a problem? Simple: Like it or not, you need several instances of the same database design. Programmers need instances to test their code. Database developers need instances to try changes. Testers need at least one instance to verify applications. Product managers need instances to close stories. You get the idea.

People tend to rationalize an environment by thinking of it as a sequence of important databases through which design is promoted. This is illustrated in Figure 1.

Thinking in terms of individual databases is a gateway to permitting manual changes to database designs. Manual changes, in turn, allow potential variations to creep in. Variations degrade the strength of the relationship between a database and its design. So try very hard not to think about the design of individual databases and, instead, think about the design all your databases share.

The best way to consider the design shared by some number of databases is as a class, with each database instance being an object of that class. Classes, in the object-oriented world, have pretty close to total control over the design of their objects. That’s less true in the almost-languages like JavaScript and Ruby but... you know... who cares? In the real languages, a class is the final and sole arbiter of an object’s design.

What’s great about defining a class of databases rather than a litter of individual databases is that you can stop worrying about a bunch of important databases and start focusing on a single, concentrated definition of your design. The design of individual databases, which is still important, becomes a consequence of the class’s design, as shown in Figure 2.

Having one, and only one, place where the design is specified for a class of databases gives you a single point of absolute control over your database design and creates the kind of consistency needed to have automated tests provide you any real value.