Architecture/design to piece together former-relational MySQL data, now in different databases

Question

By current design, most data is stored in MySQL, and we use the repository pattern in our web app as the layer to fetch data from the DB. We do the same thing when fetching relations, using our framework's models and ORM to fetch models and their relations all in one go.

Some of our tables, mostly log-like tables, have been growing massively, and we have plans to move them to Cassandra or something similar. For now, we want to do the initial work of separating out the mysql queries and putting the big data tables in a separate database connection.

We're having a hard time figuring out a clean, organized way to manage relational data when they are across different databases. For example, we can have a RelationManager class that contains methods for fetching two different related pieces of data, and returning them the way to the domain logic of the application, the way they were before, so as to minimize the refactoring in the rest of the app. But something just seems wrong about it.

Any suggestions on how other companies manage data in many databases?

Answer 1

If I were to have to do this and ensure transactional consistency, then I would build my own commit record in a standalone ACID complaint SQL DB.

So when you are ready to begin a transaction that spans multiple database systems, create a transaction and record in your transaction DB that contains the metadata of the cross-DB transaction. In theory you could even implement some kind of locking and/or rollback mechanism here if needed.

Then I'd execute the cross DB transactions, and then assuming all went well, record the completion in the standalone DB.

You'd have some flexibility here I think depending on how much effort you want to put in. But it would allow you to use transactions across the two DBs.