Home > Articles > Data

  • Print
  • + Share This
From the author of #2: Don’t Ignore History

#2: Don’t Ignore History

Many middle-tier programmers who are responsible for database development tend to think of databases only in terms of their current design most of the time. That probably stems from the fact that, in most programming endeavors, it is safe to ignore history. Got a new design to handle those complex tax calculations? Unceremoniously delete the libraries containing the old system of classes and replace it with the new one (Figure 3).

Databases hardly work that way, do they? No. A database must survive a design change because it contains vital knowledge acquired over months, years, or even decades.

What actually happens in production is an indicator of what we should be doing all the time. A deployed database undergoes pretty much the same procedure no matter what the design is and who the designer is. It starts out with whatever the initial design happens to be. After some period of time, some changes are made to meet new needs. Some more time passes, then another incremental change is applied to the design of an existing database.

If you really think about that process, you see that it can be expressed even more simply:

  1. Start with an empty database
  2. Apply the changes to get to version 1
  3. Wait
  4. Apply the changes to get to version 2
  5. Wait
  6. Apply the changes to get to version 3
  7. Wait
  8. ...

That, in turn, collapses down to the following generic process:

  1. Start with an empty database (version 0)
  2. For each version X:
    1. Apply changes to get from X-1 to X
    2. Wait some amount of time

Looking at that process, most reasonable people would wonder “Why can’t I just build all my databases that way and have the amount of time waited between steps be what varies?”

The answer is “You can and you should.”

The correct way to define your database is in terms of a series of deltas to get from one version to the next, treating an empty database as version 0, as shown in Figure 4.

Every database can then be built or upgraded along the exact same transformational path by simply applying the yet-unapplied scripts in the correct order. That is, to get a new database at version 4 of your design, you create an empty database instance and then apply the scripts for versions 1, 2, 3, and 4 in the correct order. To upgrade a production database from version 3 to 4, simply apply the version 4 script. The two databases will then have been built in exactly the same way. The unification of build paths is depicted in Figure 5.

Having all the databases in your class built the same way makes your tests more reliable because you can be assured they are testing the behavior that will actually go into a production environment.

  • + Share This
  • 🔖 Save To Your Account