为什么多值字段在关系数据库中是个坏主意 [英] 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屋！