单个固定表格与多列对比灵活抽象表格

Question

单个固定表格与多列对比灵活抽象表格

sqlmysqldatabasedatabase-designdata-modeling

30

我想知道如果你有一个包含十几种不同类型列表（商店、餐厅、俱乐部、酒店、活动）并需要不同字段的网站，是否创建表格定义如下的列会有好处？
例如：商店

shop_id | name | X | Y | city | district | area | metro | station | address | phone | email | website | opening_hours

或者采用更抽象的方法，类似于这样：

object_id | name        
---------------
1         | Messy Joe's  
2         | Bate's Motel 

type_id | name
---------------
1       | hotel
2       | restaurant


object_id | type_id
---------------
1         | 2
2         | 1

field_id | name           | field_type
---------------
1        | address        | text
2        | opening_hours  | date 
3        | speciality     | text

type_id | field_id
---------------
1       | 1
1       | 2
2       | 1
2       | 3

object_id | field_id | value
1         | 1        | 1st street....
1         | 3        | English Cuisine

当然，如果值是预定义的（例如：特长可以拥有自己的列表），它可以更加抽象。

如果我采用抽象的方法，它可以更加灵活，但查询将会更加复杂，需要进行大量的连接操作。但我不知道这是否会影响性能，执行这些“更复杂”的查询。

我很想知道两种方法的优缺点是什么。我可以自己想象，但我没有经验来证实这一点。

- Moak

2

感谢。您可能也会对**这个问题/答案**感兴趣。 - PerformanceDBA

5个回答

9

在你的问题中，你同时提出了至少两个主要问题。这两个问题是E-A-V和gen-spec。

首先，让我们谈谈E-A-V。你的最后一个表（object_id，field_id，value）本质上是一个E-A-V。 E-A-V有好处和坏处。好处是结构非常通用，可以容纳描述几乎任何主题的几乎任何数据集。这意味着您可以在没有数据分析和对主题的理解的情况下进行设计和实施，并且不必担心错误的假设。坏处是，在检索时，您必须进行数据分析以前构建数据库跳过的内容，以便提出有意义的查询。这比仅仅检索效率更为严重。但是，您还将遇到检索效率的严重问题。学习此陷阱只有两种方法：经历它或从那些经历过的人那里阅读。我建议阅读。

其次，您有一个gen-spec案例。您的表（object_id，type_id）捕获了gen-spec（概括-特化）模式，以及相关的表。如果我要在酒店和餐厅之间进行概括，我可能会称之为“公共住宿”或“场馆”。但我不确定我是否理解了您的情况，您可能正在追求比这两个名称更加通用的东西。毕竟，您在列表中包括了“事件”，而在我看来，事件不是场馆的一种类型。

我已经向其他人推荐了关于gen-spec和关系模型的阅读材料。
当两个表非常相似时，应该合并它们吗？但我犹豫是否要让您朝着同样的方向前进，因为我不确定您是否想在构建数据库之前先制定数据的关系模型。数据体的关系模型和同一数据的E-A-V模型几乎完全相互矛盾。在探索如何在数据的关系模型中表达gen-spec之前，似乎您必须做出选择。

- Walter Mitty

3

当你开始需要大量不同的实体（甚至在这之前……），nosql方案比起其他选择会更加简单。只需使用确切所需的字段存储每个实体/记录即可。

{
   "id": 1,
   "type":"Restaurant",
   "name":"Messy Joe",
   "address":"1 Main St.",
   "tags":["asian","fusion","casual"]
}

- mosheb

2

“抽象”方法更为常见的称呼是“规范化”，看起来像第三范式（3NF）。

另一种方法被称为“反规范化”，在使用规范化方法遇到速度问题时，可以成为一个有效的性能选项……但并不是首选。

- OMG Ponies

那么您的意思是应该采用规范化方法，如果性能真的成为问题，那么要么升级硬件，要么更改所有代码并创建新表？抱歉，我不太确定您告诉我的是什么... - Moak

@Moak：是的。如果不需要，进行非规范化处理就是过早优化。 - OMG Ponies

由于我不了解这些正常形式，你能否建议我应该研究第一、第二、第四、第五或其他正常形式？ - Moak

@Moak：维基百科有一篇很好的入门文章。 - OMG Ponies

1

你是如何在代码中表示列表的？我猜测使用Listing作为超类型，Shop、Restaurant等作为子类型？

如果是这样，这就是如何将子类型映射到关系数据库的情况。通常有三种选择：

选项1：每个子类型一个表，每个表中都重复了公共属性（名称、ID等）。
选项2：所有对象都放在一个表中（您的单表方法）。
选项3：超类型和每个子类型各一个表。

没有普遍正确的解决方案。我的偏好通常是从选项3开始；它提供了一种直观的结构，规范化得很好，可以轻松扩展。这意味着检索每个实例需要进行单个连接，但是RDBMS已经针对执行连接进行了优化，因此在实践中并不会导致性能问题。

选项2可以更有效地查询（无需连接），但如果其他表需要引用所有超类型实例，则会导致问题（外键过多）。

Option 1乍一看似乎是最高效的，但有两个注意点：（1）它不具备变化弹性。如果您添加新的子类型（因此具有不同的属性），则需要更改表结构并进行迁移。（2）它可能比看起来要低效。由于表格填充是稀疏的，某些数据库不会特别有效地存储它。因此，它可能比选项1不那么高效-因为查询引擎可以比搜索膨胀的稀疏表空间更快地执行联接。

选择哪种方法实际上取决于了解问题的细节。我建议您稍微阅读一下这些选项：this article是一个很好的开始。

希望对您有所帮助

- sfinnie

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- PerformanceDBA · Accepted Answer

在我们可以进行合理的讨论之前，需要澄清和解决某些问题。

先决条件解决：

1. 标签在一个要求精确的行业中，使用精确的标签非常重要，以避免混淆，并且使我们可以在不使用冗长的描述和限定词的情况下进行交流。你发布的FixedTables实际上是Unnormalised。当然，它可能是第三范式的尝试，但实际上它是一个平面文件，未经正常化（而不是“去正规化”）。你发布的AbstractTables实际上是Entity-Attribute-Value，准确地说，它几乎是第六范式，比3NF更加正规化。当然，这是在正确执行的情况下。 • 未正规化的平面文件中充满了重复项（没有采取任何措施去除重复组和重复列或解决依赖关系）和空值，它在很多方面是性能障碍，并且防止并发。 • 为了去正规化，首先必须进行正常化，然后出于某些很好的原因将正常化回退一点。由于它一开始就没有正常化，所以无法去正规化。它只是未正规化的。 • 不能说它是“为了性能而去正规化”，因为作为性能障碍，它是性能的对立面。好吧，他们需要一个缺乏正式设计的理由，“出于性能考虑”就是这个理由。即使最小的正式审查也会揭示其误导性（但很少有人能提供，所以它仍然隐藏着，直到他们找到外部人员解决了大量性能问题）。 • 正规化结构的性能远远优于未正规化结构。更多正规化的结构（EAV/6NF）比较不那么规范化的结构（3NF/5NF）性能更好。 • 我同意OMG Ponies的主旨，但不同意他们的标签和定义。 • 不是说“除非必须，否则不要去正规化”，而是说“忠实地进行正规化，不需要其他的”，以及“如果存在性能问题，则你没有正确地进行正规化”。

2. 维基百科正常形式和正规化条目的定义是不正确的；它们混淆了正常形式；它们在正规化过程方面缺乏内容；并且他们给予已经被驳斥的荒谬或可疑的正常形式相等的重视。结果是，维基百科增加了一个已经混乱且很少被理解的主题。所以不要浪费你的时间。

然而，为了进展，让我说一下，不会阻碍参考的内容。

第三范式的定义是稳定的，没有改变。
在3NF和5NF之间存在很多混淆。事实上，这是在过去15年中取得进展的领域；许多组织、学术界以及带有限制产品的供应商跳跃地创建了一个新的“规范形式”来验证他们的产品。所有这些都是出于商业利益，学术上不可靠的。原始的、未经篡改的3NF旨在保证某些属性。
总之，现在的5NF就是15年前3NF的预期目标，你可以跳过商业言谈和十二个左右的“特殊”（商业和伪学术）NF，其中一些在维基百科上有所涉及，甚至以混淆的术语描述。

第五范式
由于您已经能够理解并实施EAV，请您理解以下内容不成问题。当然，真正的关系模型是先决条件，如强键等。第五范式是因为我们跳过了第四范式：

第三范式
- 简单明确地说，每个表中的非键列与该表的主键之间存在1::1的关系，
- 与其他非键列没有关系。
零数据重复（如果规范化得到认真推进，则可实现；不是仅凭智力或经验，或通过将其作为目标而不进行正式过程来实现）
无更新异常（当您在某个地方更新一列时，您不必在其他地方更新相同的列；该列仅在一个位置存在）。
如果您理解上述内容，则可以忽略4NF、BCNF以及所有愚蠢的“NF”，它们是物理化记录文件系统所需的，这是学术界提倡的，与关系模型（Codd）完全不同。

第六范式

目的是消除缺失数据 (属性列)，也称为Null值的消除
这是处理Null问题（也称为处理缺失值）的唯一真正解决方案，其结果是一个没有Null值的数据库。（可以采用5NF和标准以及Null替代方法实现，但这并不是最优解。）如何解释和显示缺失值则是另一回事。
从技术上讲，它不是真正的规范形式，因为它没有5NF作为先决条件，但它具有价值。

EAV与第六规范形式
除了一个之外，我写的所有数据库都是纯5NF。我曾经与（管理、修复、增强）过几个EAV数据库，并实现了许多真正的6NF数据库。EAV是对6NF的松散实现，通常由那些没有很好掌握规范化和NF的人完成，但他们可以看到EAV的价值和灵活性。你就是一个完美的例子。

区别在于：因为它是松散的，并且因为实施者没有参考(6NF)可信赖，所以他们只实现所需内容，并将所有内容都编写在代码中；这最终会变成一个不一致的模型。

相反，纯6NF实现确实有一个纯学术参考点，因此通常更紧密和一致。通常这表现在两个可见元素上：

6NF具有包含元数据的目录，所有内容都定义在元数据中，而不是代码中。EAV没有一个目录，所有内容都在代码中(实施者跟踪对象和属性)。显然，目录可以方便地添加列、导航，并允许形成实用程序。
当理解了6NF时，它提供了真正的解决The Null Problem的解决方案。由于缺乏6NF上下文，EAV实现者在代码中不一致地处理缺失数据，或者更糟糕的是，在数据库中允许Null值。6NF实现者不允许Null值，并且以一致而优雅的方式处理缺失数据，无需编写代码结构（用于Null处理；当然，您仍然需要为丢失的数据编写代码）。

例如，对于具有目录的6NF数据库，我有一组过程，可以重新生成执行所有SELECT所需的SQL，并为所有用户提供5NF视图，因此他们不需要知道或理解底层的6NF结构。他们受到目录的驱动。因此变更是容易和自动化的。EAV类型的人员则需要手动完成，因为缺少目录。

讨论

现在，我们可以开始讨论了。

"当然，如果值是预定义的（例如：特殊性能可以有自己的列表），它可以更抽象"

确实。但不要太“抽象”。与其他列表一样，保持一致性并以EAV（或6NF）方式实现此类列表。

“如果我采取抽象的方法，它可以非常灵活，但是查询将更加复杂，需要很多连接。但我不知道这是否会影响性能，执行这些“更复杂”的查询。”

1.在关系数据库中，连接操作是司空见惯的。问题不在于数据库本身，而在于处理连接操作时SQL语言很繁琐，特别是涉及到复合键的情况。

2.EAV和6NF数据库的连接操作和普通的没有区别。如果必须手动编写每个SELECT查询语句，那么的确会变得繁琐。

3.通过(a)选择6NF而不是EAV和(b)实施目录，可以消除整个问题。您可以从目录(c)生成所有基本的SQL查询语句。这也消除了一整类错误。

4.连接操作有成本的说法是一个普遍的谬论。完全错误。

- 连接是在编译时实现的，CPU周期没有任何实质性的“成本”。 - 问题在于被连接的表的大小，而不在于这些表之间的连接成本。 - 在正确的PK⇢FK关系上连接两个具有数百万行的表，每个表都具有适当的索引（父键侧唯一；子键侧PK = 父FK +某些内容），是瞬间完成的。 - 如果子键索引不唯一，但至少前导列有效，则速度较慢；如果没有有用的索引，则速度很慢。 - 这与连接成本无关。 - 当返回许多行时，瓶颈将是网络和磁盘布局，而不是连接处理。

5.因此，您可以像任何复杂查询一样编写语句，没有任何成本。SQL可以处理它。

“我想知道两种方法的优缺点是什么。我可以自己想象，但我没有经验来证实这一点。”

1.在实施、使用（开发人员和用户）和维护方面，5NF（或对于那些没有进行进展的人来说是3NF）是最容易和最好的方法。

缺点在于，每次添加一个列都需要更改数据库结构（表DDL）。在某些情况下这是可以接受的，但在大多数情况下，由于变更控制得到了实施，这将会很麻烦。
其次，您必须更改现有的代码（处理新列的代码不算，因为那是一种命令式编程）：如果实施了良好的标准，那么这将最小化；如果没有，则范围是不可预测的。

EAV（即您发布的内容）允许添加列而不需要DDL更改。这是人们选择它的唯一原因。（处理新列的代码不算，因为那是一种命令式编程）。如果实施得当，它不会影响现有代码；如果没有，它就会产生影响。

当EAV实施不良时，它是可恶的，比5NF差劲的实施还要糟糕，但并不比大多数数据库更为不规范的未规范化形式（被错误地表示为“为了性能而非规范化”）更差。
当然，在保持强事务上下文时更为重要（比5NF/3NF更重要），因为列分散度更高。
同样，保留声明性参照完整性是必要的：我看到过失误的根本原因在于开发人员删除了DRI，因为它变得“太难维护”，结果就是有大量重复的3NF/5NF行和列以及不一致的空值处理。

假设服务器已经针对预期目的进行了合理的配置，则性能没有区别。（好吧，在6NF中可能只有特定的优化可以实现，而其他NF则不能实现，但我认为这超出了本主题的范围）。EAV实施不良会导致不必要的瓶颈，与未规范化形式一样。

当然，如果您选择EAV，我建议更加正式；购买全部内容；选择6NF；实施目录；生成SQL的工具；视图；一致地处理缺失数据；彻底消除空值。这将减少您对开发人员质量的依赖性；他们可以忘掉EAV/6NF等专业问题，使用视图，并集中精力处理应用逻辑。