Relationship of SQL and NoSQL entities in Symfony

Question

I use symfony on my web page, I want to use both mongodb and mysql in my database model, and i don't know how to link between a table on my sql and a document on mongodb.
Example: PERSON -- relation 1--n -- ADDRESS, where PERSON is a table on my mysql database and ADDRESS is a document on mongodb.
Is it possible? How can I do it? Thank you.

Answer 1

Not possible if you want to have as strong a relation as e.g. foreign key constraints. The two databases will never have immediate knowledge of each other's data, transactions (!), scopes, etc. You can carefully write code that synchronizes the two. One approach I find easy and reliable: (for the reference, documents are data block of NoSQL [entities in NoSQL] while records are data items of SQL [entities in SQL])

1. All documents are children of one record (or not transaction-safe).
2. All transactions are controlled by SQL.
3. Locking a record for writing implies locking all child documents.
4. Updating a document requires the parent record to be locked.
5. One NoSQL transaction (if the feature is available) is nested right inside any SQL transacion.
6. DB cross-references are simple data; don't expect either the DB or Doctrine to handle them specially.

In this manner the two databases can be used in sync, relatively safely with transactions and locking.