10

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

Community
  • 1
  • 1
Reza S
  • 9,480
  • 3
  • 54
  • 84
  • Keep in mind that someone will have to *operate* the two different databases after you're done writing the code (manage backups, security patches, etc.). I would not introduce a second data storage technology to a project just because it feels like a good fit, but only if the benefit of doing so outweighs all of the costs. – Eric J. Jul 06 '12 at 19:10

2 Answers2

9

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.

jmikola
  • 6,892
  • 1
  • 31
  • 61
  • 2
    This blog actually describes how it is implemented: http://jwage.com/2010/08/25/blending-the-doctrine-orm-and-mongodb-odm/ Thanks! – Reza S Jul 09 '12 at 04:10
  • That's actually the same content as the cookbook article I linked. Jon Wage committed it to the documentation after writing the blog article :) – jmikola Jul 09 '12 at 14:34
  • As far as I understand: the listener as defined in the cookbook article is not tied to the specified Entity of the Order, thus it will be called on every Entity load of the application. In my case that is a lot of calls of that listener. Is that intended? – agoldev Aug 29 '19 at 14:16
4

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.

Reza S
  • 9,480
  • 3
  • 54
  • 84