In the previous post I described general approaches how to define database deployment steps. Now I focus more deeply on analysis of individual steps and on identification of deployment patterns – I replace here a more familiar term database refactorings since I want to focus on database deployment instead of database redesign. However, there can be slight overlap between the two.

Deployment patterns are of two basic types, regarding database changes that they cover:

• Schema deployment patterns cover any modifications in database schema. They can be divided further into two disjunctive groups:
• Data-independent patterns are applied without requirements to modify existing data. In other words, when I need to apply data-independent pattern, I do not require to affect data being hold in existing tables. They are always deterministic (see below)
  Examples: add/drop views, functions or stored procedures, drop foreign key or column constraint
• Data-dependent patterns require existing data to be modified. The reason for modifications can be, for example, improving data quality before the pattern can be applied.
  Examples: add foreign key, add column constraint
• Data deployment patterns modify purely the data and they keep the schema unchanged. Usually they follow schema deployment patterns in order to insert data into newly introduced tables/columns. They are deterministic or dependent (see below).

Deployment patterns are of different clarity, depending on how straightforward it is to apply the pattern.

• Deterministic patterns include deployment of changes for which there is only one way how to deploy them. Sometimes, the particular pattern can be applied repeatably without negative impact on a database schema and data itself. E.g. deployment of Modify view pattern is pretty straightforward – just execute ALTER VIEW... and you are done. When you execute it twice, it does not cause a problem. Well, modifying the view might break a dependent application, of course, but regarding the database layer, there is neither a risk nor uncertainty in applying it.
• Ambiguous patterns are the ones for which it is not clear how to apply them. For instance, Split column pattern includes a recipe defining the way how to split one column into the two (imagine splitting FullName into FirstName, LastName). How should it be done? Split by spaces? What if FullName includes academic titles? Get rid of them? Make them a part of the FirstName? These questions have to be answered by a developer who knows data quality. Moreover, ambiguous patterns usually depend on existing data and they usually are not repeatable.

Ambiguities are the biggest obstacle for adopting automatic database deployment. If you succeed with reducing the number of ambiguous deployment patterns you will feel less pain in introducing continuous database deployment. There are less ambiguities when deployment patterns are small, e.g. introduce column pattern. On the contrary, when database deployment requires complex patterns (e.g. Introduce Associative Table) to be applied then the number of ambiguities increases.

The main question is: How to reduce the number of ambiguities?

• Deploy database changes often. Then there is smaller chance for introducing complexities between the original and the new database version.
• Use the right tools. Although the existing tools cannot solve all problems connected with database deployment they can provide significant help. They should detect differences between database versions. Consequently, they should assist a developer in classifying database deployment patterns. Of course, the tools should automatically resolve differences for which deterministic patterns can be applied so that the developer can focus on ambiguous problems.

Related posts:

1. Database Deployment Approaches

Tags: continuous integration, database