Mix of MySQL and Mongodb in an application

Question

I'm writing a web application using PHP/Symfony2/Doctrine2 and just finishing up the design of the Database. We have to import these objects (for ex. Projects, Vendors) into our database that come from different customers with variety of fields. Some customers have 2 fields in the project object and some have 20. So I was thinking about implementing them in MongoDB since it seems like a good use for it.

Symfony2 supports both ORM and ODM so that shouldn't be a problem, Now my question is how to ensure the integrity of the data in both databases. Because Objects in my MySQL db need to somehow be linked to the objects in the MongoDB for integrity issues.

Are there any better solutions out there? Any help/thoughts would be appreciated

Answer 1

Bulat implemented a Doctrine extension while we were at OpenSky for handling references between MongoDB documents and MySQL records, which is currently sitting in their (admittedly outdated) fork of the DoctrineExtensions project. You'll want to look at either the orm2odm_references or openskyfork branches. For this to be usable in your project, you'll probably want to port it over to a fresh fork of DoctrineExtensions, or simply incorporate the code into your application. Unfortunately, there is no documentation apart from the code itself.

Thankfully, there is also cookbook article on the Doctrine website that describes how to implement this from scratch. Basically, you rely on an event listener to replace your property with a reference (i.e. uninitialized Proxy object) from the other object manager and the natural behavior of Proxy objects to lazily load themselves takes care of the rest. Provided the event listener is a service, you can easily inject both the ORM and ODM object managers into it.

The only integrity guaranteed by this model is that you'll receive exceptions when trying to hydrate a bad reference, which is probably more than you'd get by simply storing an ID of the other database and querying manually.

Answer 2

So the way we solved this problem was by moving to Postgres. Postgres has a datatype called hstore that acts like a NoSQL column. Works pretty sweet

UPDATE

Now that I'm looking back, go with jsonb instead of json or hstore as it allows you to have more of a data structure than a key-value store.