A design pattern question regarding multiple database with slightly different structures

Question

I am working on a rewrite of a very old Perl project. This project for employee performance evaluation. Every year an employee will be evaluated by his supervisor(s).

The project was first put into use in 1994 using mysql. In 1995, a new mysql database was created for 1995 evaluation and the source code was modified. Then 1996, 1997, .....2003. In 2004, it is different. The developers use oracle....til now. See there are more than 10 databases.

A very bad thing is that each year the evaluation form (evaluation standard) is different. So the database structure is different from year to year.

Now I need to rewrite this project using Jsp/Servlet so it has a universal user interface for all years. But I have to keep the database structure, as my boss said, the data is very very very sensitive so just keep it there.

This is my situation. Could you tell me how to design this? Which design patter should be used?

Answer 1

Do the old databases ever need to be updated? If not, can you port them all to a newer system, and leave the old system intact? If the old systems are still able to be changed, can you add triggers so that they update the new system when the old one is updated? That way, you're never editing the sensitive old data, but you can at least represent it in a more modern way.

If that isn't possible, I would investigate just adding a layer of code that does the above at run-time. That is, if it needs to look up data in year 1995 or 2000, it can perform the required search (build a big case-statement), reformat the data into a structure that is the same across all databases, and return that. Then write the reverse, for when data needs to be shoved back into the right place.

It's not a specific design pattern in the singleton/strategy/factory sense, but sometimes you just need to make a multiplexer for ancient legacy stuff.

If you do a good job designing the new interface, then if they ever do port the old systems over, even just a portion of them, you will very easily be able to keep your new access point up to date.

Answer 2

I assume that the evaluation reports are only used to view the data, not to change them anymore.

If this is the case, decide the data/tables that is needed from the databases for the evaluation reports (this is possibly only a subset of the data/tables). On each database, define a view that contains the data for the evaluation report.

Of course, the view definition will be different for every database, but at least your report can rely on the structure of the view.

Separate the view as much as possible from the rest of the database layout. This still gives you the possibility to change the layout in the next years. As long as you can create a database view for the report, your report will work unchanged.