MySQL and MongoDB together: doing it right?

Hello!

I intend to use a project that needs scalable relational and structured database. In short word, I want to build a DMS with specially structured documents and some social aspects.

After doing some design, I thought of a hybrid solution using MySQL for relations and MongoDB for inner structures of objects. Here is what I came with:

The MySQL database contains the relations between objects, meaning the basic tables contain only a BINARY (12) _id field corresponding to the ObjectId in MongoDB. I'm considering duplicating some "important metadata", such as VACHAR(*) name from the MongoDB structures into the fields of their MySQL counterparts, I'm still not sure about that...

What I'm really not sure about is the ways to use both in Phalcon: I though about having three classes per object, let's say I'm referring to the User object:

  • The UserModel class referring to the MySQL part of the object with relational information (this->hasMany, this->hasManyToMany, etc. would be therre)
  • The UserCollectionclass, with the inner structure, taken from MongoDB
  • The actual User class that uses these object and simplifies their usage

I'm not exactly convinced about this approach. I'm sure there are less clumsy ways to achieve this and I'm ready to use some kind of Middleware that would handle these classes mor efficiently, but I'm stuck.

Would anyone have ideas, remarks or advice about this?

Thanks a lot!

edited 21d ago

Well, first off - thumbs up for your thoughts and ideas to develop DMS project on this stack! Very well ineed - I see too many devs who refuse the idea to mix and match two DB servers/services in the same app, and they end up with something which is awfully wrong (i.e. forcing MongoDB to be same as relational RDBMS with tremendously complex relations). Your approach seems logical to me, BUT, topic is quite serious and requires rather large amount of homework about specific project etc. In general, why you want to use MySQL/MariaDB as a dictionary (code book) for your MongoDB data/documents? There's arelady such functionality in MongoDB, at least for simple relations it works very nice: https://docs.mongodb.com/manual/tutorial/model-referenced-one-to-many-relationships-between-documents/

Check that out to see if it can fit for your DMS project. And yes, MongoDB being a document oriented DB is great choice for DMS, I was proposing same stack (PHP-Phalcon + MongoDB) for one similar project as well.

P.S. duplicating some "important metadata" does not seem like a good idea, and you should not do that. It will only create overhead with very small benfits.

Your approach seems logical to me, BUT, topic is quite serious and requires rather large amount of homework about specific project etc.

I did my homework this weekend :D I'll explain my choices later on.

In general, why you want to use MySQL/MariaDB as a dictionary (code book) for your MongoDB data/documents? There's arelady such functionality in MongoDB, at least for simple relations it works very nice: https://docs.mongodb.com/manual/tutorial/model-referenced-one-to-many-relationships-between-documents/

From what I read, it's "stock the ids of related documents in an array"; therefore, if I take the following scenario "Users <-> Groups is a MtM relationship; Remove user with ID user1 from group with ID group1".

In MySQL, the common way would be DELETE FROM user_group WHERE user_id = user1 AND group_id = group1

But in MongoDB with the approach described in your link; you would update document user1 by removing group1 from the array defined by the _groups field, then update document group1 by removing user1 from the array defined in the _users field, right?

Overall, I think a database should handle the relationships between entities , not upper layers of the application. In other words, its indeed doable, but just doesn't look "nice enough" ;)

P.S. duplicating some "important metadata" does not seem like a good idea, and you should not do that. It will only create overhead with very small benfits.

Here, I tend to disagree: duplication between two databases is not like duplication inside a single database. If I want to leverage the usage of MongoDB by classifying metadata and data (title, version, etc vs. content), I think those metadata pieces should be with the relationships, because of the simple following fact: an author of a document is at the same time some metadata and some relationship between two tables.

edited 12d ago

Here are some (non-comprehensive) details of my current MiddlewareBase class, that is extended by UserMiddleware and so forth:

    class MiddlewareBase implements EntityInterface, ResultInterface, InjectionAwareInterface, Serializable, JsonSerializable
    {
        protected $_data; // A MongoCollection object
        protected $_meta; // A Model object (linked to a MySQL table)
        protected $_baseName; // A string that is overridden during construction
        public function __construct(); // initializes 
        public function save(); //calls $this->update() if $this->_meta->id is set, $this->create() else
        public function create(); // Generates a MongoId that will help to set the $this->_meta->id and $this->_data->_id fields
        public function update(); // Calls $this->_meta->update() and $this->_data->update()
        public function __set($name) // If $this->_meta->$name exists as a field, set it, else set $this->_data->$name
        public function __get($name) // If $this->_meta->$name exists, return it, else return $this->_data->$name (can be NULL)
        public function findById($id) // Algorithm described below:
        /* $data, $meta = call_user_func($collectionClass::findFirstById, $id), call_user_func($modelClass::findById, $id) 
        if($data && $meta) { $object = new $middlewareClass(); $object->_data = $data; $object->_meta = $meta; ...;  return $object;
        elseif($data xor $meta) throw new exception();
        else return false;
        */
        public  function __call($name, $args) { returns $this->_meta->__call($name, $args); }
        public static function __callStatic($name, $args) // if $name is like "findFirstBy..." of "findBy...", handle them accordingly, else throw "Unhandled function"
        /* Implement functions needed by the interfaces: getDi, setDi, readAttribute, writeAttribute, serialize, jsonSerialize */
    }

This middleware relies on my implementation: $middlewareObject->findFirstByName($name) will work if the according MySQL table has a name column or else throw an exception, and that's expected behavior.