Nik Shenoy makes the following comment regarding my previous post about Visual SourceSafe And SQL Server:
My thought here is that there is a significant difference between a “release” and a specific file version. A release has a little more management involved and is a little more concerned with file and functional dependencies.
I agree 100% with this statement. File versions of stored procedures, views, and functions are pretty much interchangeable. However, data has historical dependencies. I can’t update data in a child table if the parent table hasn’t been created yet. A table transformation depends on all previous transformations of the same table to have been completed.
Nik’s comment describes how he is thinking about implementing the table updates. Here is an idea that I have borrowed from a UNIX-based product I used to support. I haven’t thought about this in depth, so I don’t have details worked out.
Create a table in the database that keeps track of data conversion history. The table contains a primary key column (a unique name for the conversion).
Have a master data conversion script that will contain all historical data conversions. Each conversion will have a unique name. The script will check if the conversion exists in the conversion history table. If it exists, the script does not execute that conversion. If it does not exist, the conversion is executed and a record is added to the conversion history table.
An alternative idea I have been toying with is to use the build version that we store in the database and forget the data conversion history table. A master data conversion script is still used. The master script retrieves the old build version. Each data conversion knows which build version it was created against, so it can make sure the old build version of the databsae was at or below the build version for the conversion.
Because we use exclusive checkouts, a master script may not be workable if we have two developers wanting to write conversions at the same time. Whatever mechanism we use to perform updates, we’ll need to be able to track which conversions need to be done and make sure they are done in the proper order.
We also have a problem with maintaining and updating “system” data. For example, we have a codes table that is used to populate drop-down combo boxes in our UI. We’ll need a mechanism to update these system data tables. Some tables contain a mix of developer-maintained and user-maintained data. For example, we have a table that stores system numbers (next sales order, next purchase order, next invoice, etc.). The NextNumber column needs to be preserved during an update, while the rest of the data needs to be refreshed during an update.
These are not unique problems. Every database development team faces these issues. There don’t seem to be well-documented solutions to these problems, however.