I am about to alter the several tables in a massive system which I probably only understand around 10%.
I want to add three columns. One of these is just a rename of an existing column. Part of me wants to :-
Rename the column but worried about the impact on unknown parts of the system that use the old name.
Append the three columns to the table therefore making the old column redundant (over time).
If you really must do this, go with option two for sure.
For the record, I think making a structure change to a db you do not understand is a recipe for disaster, so good luck!
Ideal solution: Get to understand the remaining 90% of the system :)
I would add the two new columns, and leave the OLD misspelled column the way it is. Even if your new logic uses your new (fixed) column, what about old reports? Old logic? Old insert/update statements?
If you don't understand the database schema DO NOT rename. Who knows what you'll break :)
One (slightly crazy) option is to add your column that is basically just a rename, copy the current data over to it, then re-design the original column so that it's a calculated field that returns the value of the new column.
That way you get the rename, but you won't remove the old column.
The safer bet is to turn that around. Make your new column a calculated value that just reads the old one and be done with it :)
Or, you could create a view that would do just about the same thing.
Search the source code for references to the column you wish to rename.
I assume all the source code is in some source repository. (Including SQL scripts.).
If you find any, you need to change them, and test that functionality.
In addition I would give the functionality related to this a reasonable test in a test environment, after making these changes.
If you don't do this, you should really go with your second option.
I think the safest way to do this is to create a new table and create new stored procedures and methods.
But it is best to understand the whole system though... Putting on bandages like this will become a big mess over time.
Creating new tables, stored procedures and methods is a cleaner mess than adding/modifying stuff to existing.
EDIT: I have worked on a few web apps that had the same problem. Multiple developers before me added and modified a bunch of stuff in their own style. I assume your case is similar.
Like I said. Best way is to understand the whole system and modify it. But if you can't, I find it better to add new modules rather than modify existing. It is easy to unplug it when something goes wrong. Also, at least now you know exactly how your module works.
If the existing app is very well written and easy to extend, I assume you wouldn't ask the question here.
So.. my final thoughts.
You know, I have worked on a system where we just left the old in place and then added the new and used that going forward. This creates a MUCH bigger mess in the long run than making the change and then changing all the refernces to the old structure. So now they have a system where they can't standardize anything new at all because there are four different structures for storing Speaker information and two different structures for storing rep information, etc. So every tiny little change must be customized by client making everything that much harder to maintain in the future. This is not efficient or effective and it becomes much more expensive to maintain. Bite the bullet and find out what will be affected and then change everything at once.