Using MongoDB and MySQL in same app (cross-database join?)

Question

Basically out of about 25 MySQL tables I have I need to switch 3 to MongoDB. Some others I can switch, but I don't really have the need for it. Then there's a huge chain of relations of about 15 tables. Out of them 4-5 would benefit from MongoDB, but about 6 I'd prefer to have in MySQL (sensible info that has to be stable).

Is it a good idea to switch part of tables to mongodb when they have relationships with others in MySQL? After the switch I guess I'd have to "join" them with mongoDB _id, is this good design?

Or should I only switch the 3 tables I absolutely need and keep the rest in MySQL?

Or should I just move everything to MongoDB? How safe is it? The reviews are kinda confusing - some say MongoDB can lose some data, some say it's as safe as any RDBMS.

Answer 1

Yes, that's a valid approach. It's not uncommon to keep your normalised/transactional data in a relational database, and use a NoSQL database like MongoDB for other types of data.

An example could be an e-commerce website. You might choose to store users and payment transactions in mySQL, but keep a product inventory in the MongoDB cluster.

By splitting data across mySQL and Mongo, you'd lose the ability to enforce referential integrity, but that's not necessarily a problem depending on your data. You can still store references to Mongo documents from mySQL, you just won't be able to enforce those relationships at the database level.

I'd start by migrating the first 3 tables, then if all goes well consider moving the others. But there's absolutely nothing wrong with the hybrid approach if you want the other data to remain in mySQL.

Answer 2

I would suggest that you use the right tool for the job. MongoDB has a few use cases where it really excels. MySQL is great where you need transactional integrity. If you don't need the benefits of MySQL, then moving everything to a single datastore could make your life easier. If you need to handle transactional integrity that can't be handled well in MongoDB, then you're just making your life harder.