1

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?

  • 1
    Do you need all the log like data? Or could you savly delete it after a given period of time? If the later is the case have a look at partitioned tables. If you organize the tables in e.g. monthly periods you can drop them after you do not need them any more. The beauty in this solution would be that there is almost not change to your existing code. – Anubis Jan 18 '18 at 11:12
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.

  • Thanks for the comment. I will keep this in mind even though inserting/updating data is mostly contained within each specific DB. Any comments on a code design/architecture for the SELECT statements, while somewhat using existing models? – timetofly Jul 21 '17 at 20:01
  • 2
    Assuming your repository pattern is well implemented, you should be able to encapsulate the data access in there for your models and allow the repositories to connect to the appropriate data store system as needed. Your models shouldn't care or be aware of which persistence system they are being written to or read from. – RibaldEddie Jul 21 '17 at 20:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.