Abstract

This paper describes a journey through identifying, understanding and managing change in database schemas objects. It describes some common terminology, describes the kind of changes that can happen to database schemas and describes some ways to record and control these changes.

Introduction

Changing application source code is hard. But there are handy tools and techniques to help developers cope with them. Assuming that these tools and techniques work well with your relational database can lead to problems. This paper attempts to explain the what, how and why of changes to databases - or more particularly the objects within them - and provide some useful pointers that should help anyone who works with databases as part of their software applications.

For the purposes of this paper I am focussing on relational databases, although the principles should apply to any application data store. Commonly used open source relational databases include MySQL, PostgreSQL, SQLite, Firebird and Ingres.

In this paper I discuss why and when your database objects will change and what to do to support these changes in as painless a fashion as possible. The database components of an application complicate configuration management. Application code (or compiled objects created from it) can be deleted and replaced, but you can't just drop and replace database objects when you want to upgrade, you have to deal with not only the containers of your data but also their contents.

Throughout this paper I am going to use some terms very specifically and it is worth defining here what I mean by them;

database

A database management system that allows an application to store data.

database schema

A logical collection of database objects within a database

database object

A construct within a database such as a table, view, index or stored procedure.

domain model

A representation of our understanding of a particular problem or subject area.

DDL

Data Definition Language. Also used to refer to one or more scripts containing a series of DDL statements.

Why Do Things Change?

Things in a software application system change mainly because what we want them to do changes. Either the functional requirements are modified with requests for new features or our understanding of the problem domain improves as we work in it.

With data models as with any other aspect of a software system there is a trade off between what you need now and designing for the future. This has been explored in many publications and the arguments won't be repeated here. However, due to the nature of data and domain models building out areas of the domain model that have not been previously implemented can be treated almost as new development. Modifying objects is rarer than changing application code because they have less vectors of change. Database objects can be added, dropped or renamed and little else. Hence database schemas, unlike application code, are unlikely