backwardly compatible database changes

Question

I had a discussion with our DBA about how to change a database schema. His opinion is that all changes must be reversible. For example:

• Obsolete tables/columns should not be dropped as soon as they become redundant. Instead, they should be kept for at least a few releases.
• Instead of renaming a table/column, create a new table/column and copy the contents from the old into the new
• When a stored proc/trigger named 'foo' needs to be modified, leave the original stored proc/trigger in place and create a new stored proc/trigger named 'foo2'. Of course, this means that all references to the stored proc/trigger must be updated to refer to the new name

The benefit of this approach is that the database can be switched to a previous version if (for example) a release fails and one needs to revert to a previous version of the application. This would not be possible if tables and columns were simply dropped.

I have my own opinions about the wisdom of this approach, but I'll keep them to myself for the time being for fear of biasing the responses. In case it makes any difference, the environment is a startup developing a social network app.

Answer 1

You don't say what software environment you are in but from enterprise (banking) work these are my views.

The general principle is correct something can go wrong with the release perhaps not the SQL code but the client code and you would need to be able to revert the server. I have seen this happen several times.

If the problem is found some time after the release say a few hours then you will have to deal with any data entered in the meantime.

It might be that a duplicate of the database taken at the release time could be updated with new data but the environment might not allow this (although this is the main way I have done large releases).

In my experience a release issue could affect one small part of the system and most of it is OK and so you do not want to shut down and revert the whole system just to recover the small part.

However given that the changes need to be reversible I think your dba is being a bit conservative.

Tables and columns should be dropped at some stage but that could wait until a later release so that you could revert back

Yes always copy the data (In fact probably best not to rename as unless the name is totally inappropiate the risk of doing a change surely out costs any benefit). If the type of the column is to be changed it depends on the SQL server and what is being done. For examle on Sybase I would allow an increase in the size of the column as that does not alter data but decreasing teh size would require a copy as data values could be affected.

As for stored procedures and triggers I would not do a renaming and just overwrite as this is like compiled code. The object you are altering does not depend on the data and so can be recreated immediately. Although this does assume that yo can easily get any previous version of the stored procedure from version control etc. (I have seen dbs where code is not under version control and the only version is in the db and then I can see the need not to overwrite the code - but I would get the code under control before the next release)

Answer 2

This sounds like the DBA is too lazy to do backups. ;)

Answer 3

I agree that you should always make backups of your database, but you also shouldn't pollute your database with useless information. The same as you shouldn't keep your code polluted with useless code.

Make a backup of the database, and then make your mods. If something happens, revert to your backup.

Keeping everything in the database all the time will lead to incredible bloat. Not only that, you can get hit by some performance issues. And MOST IMPORTANTLY, later no one will want to touch it since they won't know why it's there. Unlike code, it's way harder to figure out why there are additional columns, etc. in a database at a future date. They won't know it's legacy data/code and therefore they'll just keep maintaining it!

Answer 4

"Obsolete tables/columns should not be dropped as soon as they become redundant. Instead, they should be kept for at least a few releases."

And does he then also keep the constraints that govern those columns that he doesn't want to drop immediately ?

Meaning that update failures might possibly arise because of constraints that the user has declared are no longer part of the business rules ?

I'm kind of sympathetic to people who consistently seek to "phase out gradually and carefully". I just don't know whether that approach is tenable in a database context in all the examples you mention.