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.

Encapsulation of Database Design

It seems to me like every software developer goes through a phase where they think stored procedures are bad and a phase where they think they are good.  Like me, some alternate through these positions several times before settling on a position that works.

In the end, I discovered that there was never a dichotomy in the first place.  The real question was not whether to use stored procedures but how to use them.  The struggle over whether or not to use stored procedures was never really about stored procedures.  It was always about properly separating concerns and encapsulating designs.

In the Beginning...

I started off loving the idea of stored procedures.  I loved them based on a single argument that, at the time, was possibly relevant: performance.  That pernicious specter that looms over our industry, the myth of proactive optimization, cast its spell over me and I was totally sold.

In the nineties, it might even have been true that there was a compelling performance-based reason to write a stored procedure.  In general, for modern database systems, that is no longer the case.

Flip

As with many people in the pro-stored-procedure camp, I ran into one of the many potential failings of heavy stored-procedure use.  Putting behavior in a stored procedure because it is "faster" or "more appropriate" often drives bad design decisions.

The more logic I added to a database, the harder it was to test.  This was before I stumbled into the ideas in my book but it would still be true, at least to some extent, today.  Also, the fatter I made my database layer, the thinner I made the other layers.  The coup de grace was the fact that, on a regular basis, large stored procedures were actually slower than performing dumb selects and processing the data into meaningful results in a middle-tier service.

Essentially, every time I transferred business logic from somewhere more appropriate to a database, I made my system harder to test, less flexible, and slower.  So I did what humans often do and switch from one extreme to its opposite: no stored procedures of any kind.

Flop

The problem with toggling between extremes is that it reduces the world to binary decisions.  Of course it is true that, in any decision, one is considering a finite set of options which can be reduced to a series of binary decisions.  There's nothing wrong with choosing between two things but there is something wrong with limiting your options early in the decision-making process.

The "no stored procedures" stance didn't work out very well either, although it does work better than "everything in the database" way of working.  Instead of a tightly-wound, change resistant machine that served as the center of an application's universe and ground change to a halt, I was building delicate arrangements of glass work that shattered at the first hint of change which served as the center of an application's universe and ground change to a halt.

The problem with not having any stored procedures is that something has to have an intimate relationship with the structure of a database.  When the stored procedures go away, that tends to become whatever is connected to the database (usually a middle-tier service).

That kind of intricate understanding of implementation details between one module and another is inherently more difficult to enforce that such an understanding residing exclusively within a module.  It drives the cost of change through the roof.

The Other Way

As is often the case, the artificial binary decision was hiding the true nature of the decision in the first place.  The question was never "should stored procedures be used or not?"  The right question to ask is "for what should we use stored procedures?"  Note the "yes" and "no" answers to the former question map to answers in the latter but there are many other potential answers as well.

I don't want to iterate through all the possible ways stored procedures could be used in a database design; mostly because that list is probably infinite in length.  So I'll just cut straight to the one that I think is right: encapsulation.

A while back, someone I was working with, Christopher Taylor, suggested to me that stored procedures were the equivalent of a class's interface for a database.  They are a contract between a database and its clients.  So long as the contract is fulfilled, you are free to revise the implementation details to your heart's desire.  He didn't use these words and I don't recall the exact words he used but it the point is that it was he who turned me on to this idea.

Database Designs Require Encapsulation

Probably the most sapient portion of the observation had little to do with stored procedures, specifically.  Stored procedures are merely a tool we use in recognizing that databases need encapsulation of their designs just the same way that any other object would.

Depending on the features available in your chosen database platform, you could conceivably create encapsulation other ways.  For instance, some platforms allow updateable views.  So long as you could keep satisfying the contract specified by an earlier set of exposed tables, you can change the design of your database behind that wall of encapsulation.

As Scott Bain says, encapsulation is the first principle of design.  It should come as no surprise that this is true for database designs.

Test-Driven Architecture Article Accepted by InformIT

As you may recall some friends of mine and I are working on articles and blog entries intended to reform architecture for the modern age.  This is a quick note that the article Mike Brown and I wrote regarding the impact of TDD on architecture has been submitted to the publisher and is in the editorial phase.

I'll post something here when it is published.

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.

Knowledge, Behavior, and Information

I mention this in my book but I thought I might elaborate.  I think a useful way to conceptually divide the parts of a database is into three groups of design elements: information, knowledge, and behavior.

The Three Concepts

Information and knowledge are often confused in everyday language.  So, first, I'll disambiguate those two words.  Not all data are information or knowledge and rarely are the two interchangeable.

Information is a special subclass of data.  I'm sure someone who is an expert in communication theory or some other kind of academic would be glad to correct me and I won't fight them on what the technical definition of the word is.  I'm only interested in what the useful definition for my own purposes is, not the officially right definition.  For the purposes of this blog entry, and of everything I say and write, it is the part of a signal that the recipient did not know in advance.  Simply put, information is data which informs its recipient.

If information could be thought of as facts in transit, then knowledge would be facts at rest.  In essence, knowledge is potential information but it is also a potential driver for action.  That is, the two uses of any given object's knowledge are to inform other objects, thereby adding to their knowledge, and to inform decisions, thereby improving the value of an action taken.

That latter purpose is the perfect opening to briefly introduce the third player in the database design world: behavior.  If knowledge and information are facts at rest and in motion, behavior can be thought of as how something responds to knowledge or information.  For instance, you drive on the correct side of the road because you know you will slam into something if you do.  Likewise, you yank your hand away from a too-recently poured cup of coffee have become informed that the cup is too hot to touch without damaging tissue.

Information as Pertains to Databases

In the database world, information is the set of signals sent or received by a database.  A query and its parameters, the invocation of a stored procedure, the results set, and any errors that occurred are all examples of information as a database sees it.

In essence, information is the "surface" of a database's design.  It is impossible for external parties to access the value of a database except by sending and receiving signals.

Moreover, it is the means by which value is conveyed between a database and its clients.  It is pointless to update a database with information it already knows.  It is useless to query a database for what you already know.  Value is created by such actions that results in one of the other entities "learning" something.

Knowledge as Pertains to Databases

Knowledge is the reason why databases cannot be maintained using the simple "blast and rebuild" upgrade path we apply to most software deployment problems.  All the facts stored in a database are knowledge; not all the data, because you can introduce noise into a database's design, but all the facts.

Knowledge is the purpose of a database.  Most software products and components exist to convey facts between parties or to process data and discover new facts.  Some software exists to entertain.  Databases exist to preserve knowledge.  Each production database is a modern day Library of Alexandria, complete with the ability for some asshole to burn it down and, in so doing, to cause irreversible damage.

We have known this for a long time - as close to "forever" as matters.  Databases have always been designed around the knowledge they capture and preserve.  Those design decisions stand as a reflection of our implicit understanding that databases aren't merely data bases, but knowledge bases.

Behavior as Pertains to Databases

So what is the role of behavior in database design?  It's another one of those things that can be put simply or drawn crisply, but can take a lot of work to implement correctly.  The role of behavior in a database is to mediate between knowledge and information.

All the information that a database receives needs to be translated into knowledge and stored for safekeeping.  Why?  So that, later, that knowledge can be translated back into information to help other actors make decisions or discoveries.

You can codify the behavior of a database in many different ways.  At the time of this entry, the most common way is to couple the behavior offered by a database directly to the kinds of knowledge that database can store.  This is accomplished by creating, publicly exposing, and coupling clients to table structures and relationships.

The Relationship Between the Three

I find it useful to divide database design into three parts.  The information layer of design is where the interactions between databases and other objects are defined.  The knowledge layer of design is where the facts you want to store in a database are housed.  The behavior layer is where one codifies the manner in which facts are absorbed or emitted.

behavior translates between knowledge and information

I'll post more on each of the specific layers of design with some implementation recommendations later.