How bad is it to change the structure of big tables?

Question

My target DB is MySQL, but lets try to look at this problem more "generic". I am about to create a framework that will modify table structures as needed. Tables may contain a hundred thousands of records some day. I might add a column, rename a column, or even change the type of a column (lets assume that's nothing impossible, i.e. I might only change a numeric column into a varchar column), or I might also drop a whole column. But in most cases I would just add columns.

Somebody told me that's an very bad idea. He said that very loud and clear, but with no further argumentation. So what do you think? Bad idea? Why?

Answer 1

Databases are built to do some things very well: to handle data integrity, referential integrity and transactional integrity, to name just three. Messing with table structures (when they have existing data and relationships) is probably violating all three of those things simultaneously.

If you want to adjust the way your data is presented to the user (limiting visibility of columns, renaming columns etc.), you are much better off doing this by exposing your data via views and altering their definition as needed. But leave the tables alone!

Answer 2

In most databases, adding and renaming columns are simple operations that just change the table metadata. You should actually verify that that's the case for the MySQL storage engine you're using, though. Dropping a column should be lightweight too.

By contrast, changing the type of a column is an intensive operation, since it involves actually creating data for each row in the table. Similarly, adding a column and populating it (rather than leaving the new column's values as null).

Answer 3

It is a bad idea.

You will have client applications using these columns, so whenever you rename or remove any columns, you will break their code.

Changing the type of a column might be necessary sometimes, but that's not something you will have to do often. If the column is numeric and you do calculations with it, then you can't change it to be of type varchar. And if you don't need it for any calculations and you expect non-numeric characters later, why not define it as varchar in first place?

Answer 4

it is very harmful and should be avoided as integrity of data is disturbed and your queries will also fail incase you have hardcoded queries or improper ORM. if you are doing it be cautious and if it live take the backup of your DB first.