Currently, while creating tables, we create 4 to 5 extra columns with temp names like c0, c1, c2 etc. to be used later when needed and are left NULL by default.
This way we don't have to create additional columns when needed. We just rename one of the temp columns created in the first place.
I am wondering if this approach is good or not. Also, what is the performance difference if new columns are created vs renaming old columns.
I wouldn't say this is good practice, it will cause confusion in the long term with new members to the team. People will always have to check columns have not already been used by someone else without been renamed for example. If someone forgot to do a rename and populated the column, the rename will break existing procs referencing the columns.
Also, how do you know what value type you need in the future? This would need changing too.
Adding a new column is not an issue, I do this often with large tables of many 10's of millions of rows. Essentially a note is made in the dB engine to say new rows inserted have this new column and so the value for this column is needed to be stored on the pages behind the scenes.
Bottom line -- Extra columns are almost always more trouble and confusion than benefit.
Sure ALTER TABLE .. ADD COLUMN .. costs something, but it is usually worth it.
Another approach... Build a "parallel" table; same PRIMARY KEY, but with the new columns. This is essentially "free", but has these drawbacks:
JOIN to the new table. (Or LEFT JOIN)The advice I give: Plan on needing to rethink the entire schema after a few months. Use the current time as a prolonged prototype, then bite the bullet and add the extra columns, composite indexes, new tables, etc, etc. It will be much more satisfying in the long run.
