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?