How to modify database schema on production

When I start a project with an agile process where I don’t have a complete DER for my database, how I make changes in tables that already have data in production. For example:

Have table costumers products and sales, in the beginning sales table only have the columns id, costumerId, productId and quantity. But in the future we need to change this table, for example add a new column employeeId and that quantity that was integer for float. Can I make this changes on production database or need create an intermediate table for don’t touch in real data?

I give a simple example, but think in something bigger, that pass some sprints and nobody see the dependence… How to act in this situation?

Regards!

Answer

This is a common problem. There are several approaches, and some of them depend on the DBMS used.

Key tips:

1. Do not make backward incompatible changes to the metadata in the database. This means that you can add a field to the table, but you cannot delete it and (it depends) you cannot rename it.
2. If you change the type of field, then, in reality, you add a new field and must to solve the problem of data migration. The possibility of such a solution and the way it is implemented depends on the amount of data. On millions of records, this is not a problem. Especially if your system does not have SLA 99999.
3. Consequence of paragraph 1. If you added something to the metadata, then you should not roll back these changes after submitting the workload.
4. You must know and use the capabilities of your database. Oracle, for example, has the ability to switch between versions of schemes with different structures. Also you can make a facade over several tables with the View. It’s even may support DML.
5. Try to create a plan of bump your DB and APP version. In common scenario you have DB and some application that work with that DB. Sequence of update DB and APP in them relations is highly dependent from you system arch and SLA. It’s much easier if you can have a downtime and it’s a problem if not. You may want to update and rollback your APP independently from you DB. In that case you must provide backward-compatibility when you up your DB version. New DB changes “can wait” for new APP.