Backward Compatible Database Changes
I had a discussion with our DBA about how to change the database schema. His opinion is that all changes should be reversible. For example:
- Obsolete tables / columns should not be discarded as soon as they become redundant. Instead, they should be kept for at least a few releases.
- Instead of renaming the table / column, create a new table / column and copy the content from the old to the new
- When the stored proc / trigger named 'foo' needs to be changed, leave the original saved proc / trigger in place and create a new saved 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 advantage of this approach is that the database can be switched to a previous version if (for example) the release fails and needs to be reverted to a previous version of the application. This would not be possible if tables and columns were simply dropped.
I have my own opinions on the wisdom of this approach, but I will keep them to myself for a while, for fear of bias in the answers. In case that makes any difference, Wednesday is a startup developing a social media application.
source to share
You don't say what software environment you are in, other than from entrepreneurial (banking) work, these are my views.
The general principle is correct, something might go wrong with the release, perhaps not with the SQL code, but with the client code, and you will need to return the server. I've seen this several times.
If the problem is discovered some time after the release, say a few hours, you will have to deal with any data entered at that time.
Perhaps a duplicate database made at release time could be updated with new data, but the environment might not allow this (although this is the main way I've done large releases).
In my experience, a release issue can affect one small part of the system, and most of it is fine, so you don't want to shut down and revert the entire system to restore a small part.
However, given the changes should be reversible, I think your dba is a bit conservative.
Tables and columns need to be dropped at some point, but this may wait until a later version so you can go back
Yes, always copy the data (in fact, it is probably best not to rename, unless the name is completely untrue, the risk of committing a change will necessarily lead to any benefit). If the column type needs to be changed, it depends on the SQL server and what is being done. For the example on Sybase, I would allow increasing the column size as this does not change the data, but a copy will be required to reduce the size as the data may be affected.
As far as stored procedures and triggers are concerned, I would not rename and just overwrite as it looks like compiled code. The object you are modifying is data independent and therefore can be recreated immediately. Although this assumes that yo can easily get any previous version of the stored procedure from version control, etc. (I saw dbs where the code is not under version control and the only version is in the db and then I see that there is no need to rewrite the code - but I would get the code under control until the next version)
source to share
I agree that you should always make backups of your database, but also shouldn't pollute your database with useless information. The same as you should not contain code polluted with useless code.
Back up your database and then build your mods. If something happens, go back to your backup.
Storing everything in the database all the time will lead to incredible bloat. Not only that, you may run into some performance issues. AND MOST IMPORTANT, no one will want to touch him later, since they won't know why he is there. In contrast to the code, it is even more difficult to figure out why there are additional columns, etc. In the database. They will not know its stale data / code and therefore they will just support it!
source to share
"Obsolete tables / columns should not be discarded as soon as they become redundant, instead they should be kept for at least a few releases."
And does it also preserve constraints that govern those columns that it doesn't want to throw right away?
What does it mean that update failures can occur because restrictions that the user has declared are no longer part of the business rules?
I sympathize with people who have consistently striven to “gradually and carefully“ gradually stop. ”I just don't know if this approach is supported in the context of the database in all the examples you mention.
source to share