I am currently working on a project with the final aim of converting a legacy system to a true N-Layer architecture. The initial part of the project involves converting the underlying database to a true relational design.
The underlying database is currently running on the IBM iSeries. The tables are defined using DDS and contain mountains of redundant data, no integrity checking and poorly designed keys etc. Basically refactoring them to a fully normalized design is a non-starter.
New tables are going to be designed from scratch. They will also be on the same iSeries but will be defined with DDL. This will also involve re-writing any insert or update code throughout the application to utilise the new tables. There is however a large amount of legacy apps responsible for reports, displays etc that will not be re-written at this point and will still be reading from the original tables. So we need to keep the data in old, legacy tables in sync with new table data. I was wondering if anyone had ever done something similar or had any suggestions? I am currently thinking either:
1) The stored procedures that inserts, updates, deletes from new table A will also do the same to matching legacy table B. At some point down the line the stored procedure will need to be modified to stop syncing table B
2) Place triggers on table A that also modifies table B. Then the triggers will be removed down the line... the stored procedure don't need to be changed but will this still work fine with transaction management?
3) Remove legacy table B and recreate it as view on table A. Not sure if this will work as table B works on keyed access and I believe views don't support this?
Would be interested to hear anyone's thoughts?
Cheers
I favour number 3. I assume that you would run some conversion process that would take the existing data from legacy table B and put it into new table A (or more likely, a set of new tables since it sounds like legacy table B is denormalized).
If your legacy apps responsible for reports, displays etc don't need to do any updates then using views is the ideal situation. I think keeping two separate copies of data (one normalized, one denormalized) would turn into a headache, especially when the manager's TPS reports don't match what he entered in the new system. At least with only one copy of the data, you don't have to worry about performing data fixes in two places.