Why a multi-valued field is a bad idea in relational databases

Question

When working with Mongodb and Solr / Lucene, I begin to wonder why a multi-valued field for relational databases is (generally) considered a bad idea?

I know the theoretical basis of a relational database and normalization . In practice, however, I came across many use cases when I end up using the meta table of key-value pairs to supplement the main table, for example, in tagging cases where I wish I had to do multiple joins to find the data . Or when requirements suddenly changed from the need to support one author to several authors in each article.

So, what are some of the disadvantages of having multiple value fields, or has the provider decided not to support it because it is not part of the SQL standard?

Answer 1

The main disadvantage is query bias. The phenomenon that such databases are usually developed taking into account one particular type of request and are difficult to process when other requests need to be written.

Suppose you have Students and Courses, and you simulate all this so that you can say in one row in one table: "John Doe accepts {French, Algebra, Relational Theory}" and "Jane Doe accepts {German, functional computing , relational theory}. "

This makes it easy to request “all the courses that follow ...”, but try to imagine what it takes to answer the question “what are all the students who follow the relational theory”.

Try to imagine everything that the system itself should do to give such a request (if you could write) any chance for a reasonable execution ...

Answer 2

The supposed bias suggests that SQL is always a good query language. The fact is that this is sometimes an excellent query language, but it has never been one of the most suitable sizes. Multiple-valued databases allow you to pack multiple values ​​and process "alternative perspective" queries. Examples of MVDB: UniData http://u2.rocketsoftware.com/products/u2-unidata , OpenInsight http://www.revelation.com/ , Reality http://www.northgate-is.com/ . There are many others. Their query languages ​​support what you are looking for.

Answer 3

I think this has its roots in the fact that there is no simple and standard way to map a collection to a column in the world of relations. The mutifield value is basically a simple set (an array of strings in most cases), which is hard to represent as a column. Some RDBMSs support this using a delimiter, but then again begin to feel like an anti-pattern, even if the database driver allows the use of multi-valued fields in a relational database. Databases, such as MongoDB, rely on a JSON-like structure to define data, where collections are easily matched and retrieved.