What's the best way to deprecate a column in a database schema?

Question

After reading through many of the questions here about DB schema migration and versions, I've come up with a scheme to safely update DB schema during our update process. The basic idea is that during an update, we export the database to file, drop and re-create all tables, and then re-import everything. Nothing too fancy or risky there.

The problem is that this system is somewhat "viral", meaning that it is only safe to add columns or tables, since removing them would cause problems when re-importing the data. Normally, I would be fine just ignoring these columns, but the problem is that many of the removed items have actually been refactored, and the presence of the old ones in the code fools other programmers into thinking that they can use them.

So, I would like to find a way to be able to mark columns or tables as deprecated. In the ideal case, the deprecated objects would be marked while updating the schema, but then during the next update our backup script would simply not SELECT the objects which have been marked in this way, allowing us to eventually phase out these parts of the schema.

I have found that MySQL (and probably other DB platforms too, but this is the one we are using) supports the COLUMN attribute to both fields and tables. This would be perfect, except that I can't figure out how to actually use it in a meaningful manner. How would I go about writing an SQL query to get all column names which do not contain a comment matching text containing the word "deprecated"? Or am I looking at this problem all wrong, and missing a much better way to do this?

Answer 1

Maybe you should refactor to use views over your tables, where the views never include the deprocated columns.

Answer 2

"Deprecate" usually means (to me at least) that something is marked for removal at some future date, should not used by new functionality and will be removed/changed in existing code.

I don't know of a good way to "mark" a deprecated column, other than to rename it, which is likely to break things! Even if such a facility existed, how much use would it really be?

So do you really want to deprecate or remove? From the content of your question, I'm guessing the latter.

I have the nasty feeling that you may be in one of those "if I wanted to get to there I wouldn't start from here" situations. However, here are some ideas that spring to mind:

1. Read Recipes for Continuous Database Integration which seems to address much of your problem area

2. Drop the column explicitly. In MySQL 5.0 (and even earlier?) the facility exists as part of DDL: see the ALTER TABLE syntax.

3. Look at how ActiveRecord::Migration works in Ruby. A migration can include the "remove_column" directive, which will deal with the problem in a platform-appropriate way. It definitely works with MySQL, from personal experience.

4. Run a script against your export to remove the column from the INSERT statements, both column and values lists. Probably quite viable if your DB is fairly small, which I'm guessing it must be if you export and re-import it as described.

Answer 3

Mike Woodhouse's point #2 is great. Using MySQL's built-in features for removing columns will be better than dumping and reimporting your database: it will be faster and it will not require any downtime for your database.

According to the MySQL Manual's ALTER TABLE Syntax section, this is how you do it:

ALTER TABLE `table_name` DROP COLUMN `column_name`;

You can also use ALTER TABLE to create columns, rename columns, change column data types, and change column default values. So you can make all possible schema modifications to your database without the need for dumping the entire thing.