Should Database Design Be Incremental?
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: