Mysql – From a backup strategy perspective, would it be efficient to use a relational DB (as MySql) and a document oriented DB (as MongoDB) in the same app

database-designmariadbmongodbMySQLmysql-8.0

I am developing an app where I need to store an undetermined number of properties for any given entity, for example:

  Entity    || # of properties**
  Apples    ||  14
  Oranges   ||  4
  Pears     ||  12

Using a relational DB I could set a primary key for each entity and have as many rows as there are properties, but, at the best of my knowledge, I could consider that approach only if the number of properties is fixed and known in advance, but in this case there can be rows with as few as 0 properties or as much as 99.999, so I would end up having a lot of nulls hence I was thinking on setting up a document oriented module of the APP (MongoDB) to address this and it would go as follows:

{
  'Apple':{'Property1','Property2','Property3','Property4',...},
  'Orange':{'Property1','Property2','Property3','Property4',...}
}

I intended to relate the two databases via a primary key but I am not sure if mixing 2 types of databases is a good idea , hence the question.

Best Answer

In the normal day-to-day operation of the application this will be OK. There will be a second client library and connection syntax wrapped up in the data tier somewhere and the business logic won't even notice.

Your biggest problem will be maintaining consistency between the two data stores. It is unlikely you will be able to have a transaction cover both and either commit or rollback both. Hence you may end up with inconsistencies. If you can structure the app such that it can write to one DB, commit, then write to the other DB you will be OK.

A similar problem will be backups and DR. Each DBMS will have its own backup and restore mechanisms. It will not be possible to produce a consistent backup of both, or to restore both to a single point-in-time. Reconciling the two after a restore will be a manual process.

The app will also have increased network traffic since it must send messages to two different back ends to retrieve one logical entity. This may not be significant depending on rate of queries, latency and sensitivity to delay.

A better solution may be to store the dynamic attributes in a long string (if queries do not need to inspect these attributes' values), to use the JSON features built into MySQL, or implement some form of table inheritance.