MYSQL Event to update another database table

Question

Hey All,

I have just taken over a project for a client and the database schema is in a total mess. I would like to rename a load of fields make it a relationship database.

But doing this will be a painstaking process as they have an API running of it also. So the idea would be to create a new database and start re-writing the code to use this instead. But I need a way to keep these tables in sync during this process.

Would you agree that I should use MYSQL EVENT's to keep updating the new table on Inserts / updates & deletes??

Or can you suggest a better way??

Hope you can advise !!

thanks for any input I get

Answer 1

Hi Lee,

I had the same problem in my project. I did the same thing like you - writing the whole database new. We developed the new database and the fitting code and after finishing that work we made a migration script (small application) which did the migration from old to new database.

Hope this gives you some ideas...

Answer 2

I had the same problem as well, and went by the way of duplicating data at the point of user input (basically saved to both databases at once, since the original schema lacked all the required information). After several months of development the customer realized that he is going to move to a completely new database and considered it too risky (and I agree with him).

Thus, I would advice to utter clearly to your customer that you are going to implement a new database instead of iterative refactoring of the current one.