I have MySQL databases, each with the same schema (table structure and relationships exactly the same).
The data in the Green database is shared by the other three. I have a nightly process that finds changes in the Green database and UPSERTs them into the other three databases.
Think of the Green database as reference data updated by a separate team, but there is a lot of it and it changes every day.
The end result is that each of the Blue, Red, and Purple databases are a mix of updatable records unique to it and the recods from the Green database that may be referenced but not modified.
The custom syncing process is messy and unreliable in ways that are not important for the question.
Is there a better way to combine the shared (green) data with other databases? I'd like to have minimal impact on the application data access layer that is designed for the interleaved data that is made possible by the nightly syncing process.
I thought about leaving the Green data in its own schema (on the same MySQL server) and creating a view, (for queries) for each table in my other databases to give the illusion of the original merged tabes:
CREATE VIEW blue.vw_table001 AS SELECT * FROM blue.table001 UNION SELECT * FROM blue.table001;
This would allow me to simply replace my Hibername mapping to the original tables with mappings to the corresponding view. Then writes would reference the actual tables as they do today.
However, this creates two problems...
- I'd have to eliminate all foreign keys because records in the Blue database can reference records in the Green database.
- I'm told the performance of combining the data this way will not be good. It may be important to know that there are some complex queries that join across several tables that would now be joining across views.
While it would be nice to not replicate the Green data into the other three (and there could be many more than three someday), I'm OK with that as long as the mechanism is relatively simple and reliable (my java solution is pretty complicated and error-prone).
Also, you may be wondering about primary key collisions between records created in the Green database and each of the other three. Our nighly sync logic records the Green records' original ID in a special column and then assigns a locally unique ID to the records.