3

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!

  • I do not see how a very standardized format like e-mail address or city can be classified as "multiple possible fields"! – James Anderson Nov 25 '14 at 7:27
  • By that I mean a person can have multiple email ids, might have a house in 2-3 cities, etc. I just edited the question too. – dotslash Nov 25 '14 at 7:35
  • 1
    Related, maybe a duplicate: programmers.stackexchange.com/questions/90877/… – Doc Brown Nov 25 '14 at 8:43
  • Note that if you store address from multiple countries, (physical) addresses will vary hugely in format anyways... – Clockwork-Muse Nov 25 '14 at 9:52
7

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.

  • Very valid point. Yes, maybe I'm overreacting and can easily live with a little added complexity. Thanks for the answer. :) – dotslash Nov 25 '14 at 9:59
1

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.

  • Actually, what type of city it is will not be a concern. The point is how to handle the situation where many fields with multiple values are possible. Should this be done through an RBDMS schema? Or should I store values separated by delimiter, or will NoSQL be better? – dotslash Nov 25 '14 at 7:43
  • Maybe I misunderstand this answer, but email adresses have typically no relation to physical adress locations, so how is this supposed to solve the problem? – Doc Brown Nov 25 '14 at 8:17
  • Two new types "email" and "mobile" perhaps? For serious systems there is usually a separate table for "electronic address". – James Anderson Nov 25 '14 at 9:01
  • @JamesAnderson Yes, I'm beginning to see that multiple tables is not that bad an idea, after all. Thanks for the inputs. :) – dotslash Nov 25 '14 at 9:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.