Database Design - Modifying Tables In Production

[proggR]

This question doesn't really have any immediate applications but its been puzzling me for a little bit now and I haven't found anything online yet so I thought I'd ask.

Say you're working at a company that has numerous databases and hundreds of tables. The DBA that first setup everything decades ago clearly didn't normalize these tables or design the structure very well. There are hundreds of applications and mainframe batch jobs that access these tables on a regular bases. You want to fix the data structure so that any development that is done in the future will have an easier time and won't have to navigate these crippled tables.

The problem is since there are so many applications that access these tables, any change to the table structure will most definitely affect every application and they will all need modification and testing.

Is there any way to get around changing all the programs if you're changing table structures? There will be all the same data, it will just be represented better. I'm thinking maybe you could make some kind of interface that accepts old queries and returns what the expected results would be based on the new tables but I don't know exactly how that would be done. Surely this is an issue that people have had before though. I doubt everyone has been lucky enough to have a well designed database from the start.

Any advice or pointers would be appreciated.

Thanks in advance

[ignace]

You want to fix the data structure so that any development that is done in the future will have an easier time and won't have to navigate these crippled tables.

 

Create views from this database that holds the ideal design and let future applications use these views temporarily (that's if your applications only reads data). Try to migrate as many as possible. Once most are migrated you can start normalizing (or further de-normalizing) your tables and replace the tables with the views.

 

If you are in luck and your application uses something like a database abstraction layer, object-relational mapper, table gateways, .. it becomes quite easy to shift the underlying structure without having to re-write your entire application.