When developing a new system that is not in production, database design can easily be incremental without much thought. In production systems, however, changes to the database are not as pleasant.

If the database is an interface point between multiple systems, it seems like its worth spending the time to sit down with all involved parties and hammer out the database schema in an upfront fashion. Of course, this doesn’t eternally prevent changes to the database, but it can help standardize an interface. Another approach to standardization of a database-based interface is to use a view, a separate table, or a separate database. Then any database changes that are made to the system must be updated in the mapping that creates the interface (e.g. the view must be updated).

Here are the main types of changes that I have come across and how I’ve handled them:

Changes to the application, no changes to the database

Code changes

Data Changes

Schema Changes

Field name changes

Added fields

Removed fields

Datatype changes

Fields moved to other tables (usually when normalizing)

It’s schema changes that make it difficult when in production. Here are the steps that you have to take when you make changes to the schema: