为什么多值字段在关系数据库中不是一个好主意 [英] Why is multi-value field a bad idea in relational databases

问题描述

与Mongodb和Solr/Lucene一直在合作，我开始怀疑为什么关系数据库的多值字段(通常)被认为是一个坏主意?

Having been working with Mongodb and Solr/Lucene, I am starting to wonder why multi-value field for relational databases are (generally) considered an bad idea?

我知道关系数据库和规范化的理论基础.但是在实践中，我遇到了很多用例，最终用键-值对的元表来补充主表，例如在标记的情况下，我希望不必进行多次联接查找数据.或者，需求突然从必须支持一位作者变为每篇文章需要多位作者.

I am aware of the theoretical foundation of relational database and normalization. In practice, however, I ran into many use cases where I end up using an meta table of key-value pairs to supplement the main table, such as in the cases of tagging, where I wish I don't have to make multiple joins to look up the data. Or where requirements suddenly changed from having to support an single author to multiple authors per article.

那么，具有多值字段的缺点是什么?或者供应商是否选择了不支持它，因为它不是SQL标准的一部分?

So, what are some disadvantages of having multi-value fields or did the vendor choose not to support it since it not part of the SQL standard?

推荐答案

主要缺点是查询偏差.这样的数据库倾向于在设计时考虑一种特定的查询，而当需要编写其他查询时，这种处理变得很困难.

The main disadvantage is query bias. The phenomenon that such databases tend to get designed with one particular kind of query in mind, and turn out to be difficult to handle when other queries need to be written.

假设您有学生和课程，并且对所有内容都进行了建模，以便可以在一张表的一行中说"John Doe接受{法国，代数，关系理论}"和"Jane Doe接受{德语，功能计算，关系论}".

Suppose you have Students and Courses, and you model all of that so that you can say, in a single row in a single table, "John Doe takes {French, Algebra, Relational Theory}" and "Jane Doe takes {German, Functional Computing, Relational Theory}".

这使查询"...之后的所有课程是什么"变得很容易，但是请尝试想象一下如何得出遵循关系理论的所有学​​生"的答案.

That makes it easy to query "what are all the courses followed by ...", but try and imagine what it would take to produce the answer to "what are all the students who follow Relational Theory".

尝试并想象系统本身应该做的所有事情，以给出这样的查询(如果可能的话)，使其有可能合理地执行...

Try and imagine all the things the system should itself be doing to give such a query (if it were possible to write it) any chance of performing reasonably ...

这篇关于为什么多值字段在关系数据库中不是一个好主意的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！