How to keep two databases with different schemas up-to-date

Question

Our company has really old legacy system with such a bad database design (no foreign keys, columns with serialized PHP arrays, etc. :(). We decided to rewrite a system from a scratch with new database schema.

We want to rewrite a system by parts. So we will split old monolithic application to many smaller ones.

Problem is: we want to have live data in two databases. Old and New schema. I'd like to ask you if anyone of you knows best practices how to do this.

What we think of:

1. asynchronous data synchronization with message queue
2. make a REST API in new system and make legacy application to use it instead of db calls
3. some kind of table replication

Thank you very much

Answer 1

I had to deal with a similar problem in the past. There was a system which didn't have support but there was people using it because, It had some features (security holes) which allowed them certain functionalities. However, they also needed new functionalities.

I selected the tables which involved the new system and I created some triggers for cross update the tables, so when I created a register on the old system the trigger created a copy in the new system and reversal. If you design this system properly you would have both systems working at the same time in real time.

The drawback is that while the both system are running the system is going to become slower since you have to maintain the integrity of two databases in every operation.