Should I use relational or noSQL databases for this problem

Question

I'm working on a project where one part is to save data of different people. A typical person might have the following structure:

Email: Multiple values possible
City: Multiple values possible
Contact no.: Multiple values possible
...

As you can see, the point here is the possibility of multiple values for different fields. If I store this in a relational database (say, MySQL), I think queries will become a mess because I'll have to either maintain too many tables with foreign keys, or store delimited values (which I find extremely ugly and inconvenient).

Will a NoSQL database, such as MongoDB, be a better solution here? That's because I think data can then be stored in objects, and multiple values can be handled easily as arrays within arrays, etc.

Will this be a good decision or do you see some problems in the long run? The volume of transactions will not be too much (say, a few hundred a day at most).

Thanks in advance!

Answer 1

Actually, I would not change the whole type of database system because there is one minor problem with modeling one artifact. Assumed you will stick to a relational DB system, you have the following options:

• live with multiple tables for each type of information about persons. Yes, some queries may become a little bit more complicated, but to my experience, that does not mean they "become a mess", that is an exaggeration. If you need the same kind of complictated JOIN over and over again, add some views to the model providing the JOINed data and allowing omre simple queries.

• live with delimited values (typically, only for some of your information fields). That's acceptable if you not intend to use that data in a WHERE condition, and you not intend to modify parts of that data by SQL.

• store the complex information like a list of mail adresses in XML or JSON strings. Some relational DB systems directly support such kind of attributes (most modern relational DBs have some kind of XML support, some support even JSON). This will also allow you to store more complex information like a whole address in one attribute, with different structure variants for each person

• make a more generic relational model with tables InformationField (n:1 relation to Person) and InformationFieldType (1:n relation to InformationField). This will allow you to add an arbitrary number of information fields to persons at run time without changing the DB schema afterwards (somewhat similar to a NoSQL DB). The drawback is that some queries become indeed even more complicated.

Answer 2

What you really need is an additional "address type" column containing values like "home", "work", "summer home", "winter home" etc. and have several "address" rows per person.

You will need something like this to decide which address is the most useful for a given use case.