7 Steps to Database Change Control

Change control isn't just for application development. Database schemas need change control as much as any other component in your IT organization. Database schemas—tables, columns, data types, functions, stored procedures, views, and so on—aren't static and will naturally change to accommodate the varying needs of the organization. As a company targets new markets, expands its mission, or modifies a line of business, its database has to be able to support these endeavors, which sometimes means changes to the database. The trick is to manage those changes carefully.

If you don't have a way to manage changes to a database, you run the risk of throwing the database schema into a non-normalized condition, which can cost you disk space and performance. You might create triggers and procedures that interfere with existing processes, possibly causing data corruption in stored data and generated reports.

Usually, the physical database administrator manages changes to a database schema. Having one person or group responsible for making changes to the schema is a good practice because this person or group usually has the most intimate knowledge about the database. Nevertheless, when changing one or more tables, you can easily forget something, leave something out, or overlook an impact that a seemingly minor change can have. You might also underestimate the length of time needed to make a change to a database. In the absence of special software to assist with change control, you can use a spreadsheet-based change-request checklist form like the one that Figure 1 shows.

1. In this form, you can map out proposed database changes before making them, using one form for each table involved in the change request.

2. You can assign a priority to each change by using the A to F scale, with an A being of immediate importance and an F meaning that the change can happen anytime.

3. You can research which objects in the database each change will affect, then estimate the amount of time required for making the changes. You can easily convert that time to cost. If the priority depends on the amount of work involved in making the change, delay setting a priority until you calculate the amount of work involved.

4. The top part of the checklist contains information about the change request in business terms. A business requirements analyst, an applications development specialist, or just about anyone outside the database group can fill it out. In this section, you'll see entries like "Add product profit margin with a default value of 15%" for Description of change request. You need to capture the date of the change request, the businessperson who "owns" or initiated the request, and the technical person relaying the request, if appropriate. You also need to record a business reason and a technical reason for the change request. Business reasons could include "enhance profitability potential"; technical reasons might be "support business goals," "enhance performance," or "maintain data integrity."

5. It's not up to the business or technical person named in the upper part of the form to specify which database objects need changing. That's the job of the database designer or administrator, and only this person should fill in the bottom part of the checklist. This area contains impact-analysis information, starting with the table and columns that need changing, the estimated time to make the changes, the date the changes were made, and the actual time it took to make the changes. In addition to tracking schema management, this form can help you get a handle on how much time you're spending on making database changes.

6. If a change involves more than one column in a table, insert a new row for each Table.column affected and list each column separately. In addition to the table and columns involved in a change request, your change can affect other objects, such as indexes, foreign keys, default and check constraints, functions, triggers, stored procedures, views, and scripts. You need to list these on the form. Again, if changes will affect more than one object, insert a new row for each into the checklist, so that you list only one object per row. Having a separate row for each object makes change tracking easier.

7. At the bottom of the checklist are rows for listing any relevant reference document (e.g., a functional specification) where the change request originated, the total number of database objects involved in the change, and the estimated total time to make all the changes.

When you use this change-request checklist consistently, you'll have a searchable record of all database modifications, when you made the modifications, how long it took—and how much these changes took out of your operational budget. Happy tracking!