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?

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply