Supporting a Database Refactoring with Tests and Views

As we develop a database application, we often realize that decisions made early on regarding normalization need to be changed. A recent post on the Agile Databases mailing list illustrates this need.

Let's suppose we have an application that stores the office hours that employees make free for questions. The initial design allowed employees to enter their office hours in free form text and was stored in the following schema.

It wasn't until recently that I ever thought to do away entirely with database constraints. Aside from appropriate normalization and writing fast SQL code, much of the time database professionals spend is on figuring out exactly what constraints to put on their schema. We're taught by textbooks and by some bad experiences that database constraints are a good idea. But how do they fit in with our new, agile view of the world?

Test Driven Development (TDD) is known to be a successful method of object oriented development. In database development however, TDD practices are not wide-spread and development teams struggle with applying the TDD principles to the SQL language. This is a problem, because it leads to poorly tested code. In turn, not having the appropriate test cases, makes it difficult to improve your existing database design. Not implementing TDD practices in the database, overtime, leads to a decaying architecture and can hinder the evolution of the overall application.