Changing Database Table Column Structure The Right Way

Some database (and web apps) grow organically. They start small and then features keep popping up and the database schema needs change from time to time.

Altering the database table column structure using ALTER is easy, but what if the database is used by multiple users and external tools. They probably depend on the table column schema and changing it would make their queries break.

Solution for database schema backward compatibility

One solution that is often used in this case is to add new columns, but keep the existing ones unchanged. This guarantees that new queries can be given more optimal database table structure using new columns and the old queries running won’t break.

You can even have a phase-out period, during which you can work on changing the older queries and getting them up-to-date to follow new DB table structure. Once all old queries have been migrated, you can DROP those old columns and enjoy your new database schema fully.