具有多个列的单个固定表与灵活的抽象表

Question

我想知道您是否有一个网站，其中包含需要不同字段的十几种不同类型的列表（商店，餐厅，俱乐部，酒店，活动），是否可以使用创建
示例列的表来创建表？

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

当然，如果预定义了值，可能会更抽象（例如：专业可以有自己的列表）

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

我想知道这两种方法的优点和缺点。我可以自己想象，但是我没有经验来确认这一点。

Answer 1

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

前提条件解决

1. 标签
   在要求精确的行业中，重要的是我们使用精确的标签，以避免混淆，以便我们可以进行交流而不必使用冗长的描述和限定词。
   。
   什么你已经张贴FixedTables，是 Unnormalised
   。足够公平，可以尝试使用“第三范式”形式，但是实际上它是一个平面文件，非规范化（不是“非规范化”）。确切地说，您发布为AbstractTables的是
   Entity-Attribute-Value ，几乎，但不完全是第六范式，因此比3NF规范化得多，当然，假设正确完成。
   
   

   • 未规范化的平面文件未“非规范化”。它充满了重复（没有做任何操作来删除重复的组和重复的列或解决依赖项）和Null，它在许多方面都是性能浪费，并防止了并发。

   • 为了进行Denormlaise，必须先对其进行归一化，然后出于某些充分的理由而使归一化稍微后退。由于首先没有对其进行归一化，因此无法对其进行归一化。它只是未归一化。

   • 不能说它是“为了性能”而被非规范化的，因为它是性能猪，它与性能完全相反。好吧，他们需要缺乏形式化设计的理由]，“为了性能”就可以了。即使是最小的正式审查也暴露了错误的陈述（但是很少有人可以提供，因此它一直隐藏着，直到他们让局外人解决，您猜对了，这是巨大的性能问题）。

   • 规范化结构的性能远优于未规范化结构。标准化程度较高的结构（EAV / 6NF）比标准化程度较低的结构（3NF / 5NF）更好。

   • 我同意OMG小马的主旨，但不同意其标签和定义

   • 而不是说 “除非必须，否则不要“非 正规化 ”” ，而是说 “忠实地标准化，定期” 和 “如果存在性能问题，则表示您未正确标准化” 。
     。

2. Wiki
   关于Normal
   Forms和Normalization的条目完全是个笑话。具体来说，这些定义是不正确的。他们混淆了普通表格；他们对规范化过程一无所知；它们对很久以前就被揭穿的荒谬或可疑NF给予同等的重视。结果是，Wiki增加了一个本已混乱且鲜为人知的主题。因此，不要浪费您的时间。
   。
   但是，为了取得进展，在没有该提法构成障碍的情况下，我要说这句话。

   • 3NF的定义稳定，没有改变。
   • 3NF和5NF之间存在很多NF混淆。事实是，这是过去15年中取得进展的领域。许多组织，学者和供应商都对其产品进行了限制，他们跳起来创建了一个新的“普通表格”以验证其产品。所有服务于商业利益和学术上不健全。3NF处于其原始未篡改状态，旨在并保证某些属性。
   • 总的来说，今天的5NF就是15年前3NF的目标，您可以跳过商业玩笑和两者之间的大约十二种“特殊”（商业和伪学术）NF，其中一些是在Wiki中识别，甚至用混淆的术语表示。
     。

3. 由于您已经能够理解和实施帖子中的EAV，因此理解以下内容将没有问题。当然，真正的关系模型是先决条件，强键等。 第五范式 是，因为我们跳过了第四种：

   • 第三范式
   • 简单来说，每个表中的每个非键列与表的主键之间具有1 :: 1的关系，
   • 并且没有其他非关键列
   • 零数据重复（结果，如果勤奋地进行标准化，则不是单靠智力或经验，或者是通过努力将其作为目标而没有正式过程来实现）
   • 无更新异常（当您在某处更新一列时，不必更新位于其他地方的同一列；该列存在于一个且仅一个位置）。
     。

4. 第六范式 当然是第五范式，再加上：

   • 消除丢失的数据（列）。这是Null问题（也称为处理缺失值）的一种真正解决方案，结果是没有Nulls的数据库。（这可以在5NF下使用标准和Null替代品完成，但这不是最佳选择。）如何解释和显示缺失值是另一回事。
     。

5. EAV与第六范式
   的比较我编写的所有数据库（除一个以外）都是纯5NF。我已经使用（管理，修复，增强）了几个EAV数据库，并且已经实现了一个真正的6NF数据库。EAV是6NF的宽松实现，通常由对标准化和NF不太了解但可以看到EAV的价值并需要EAV灵活性的人员完成。你是一个完美的例子。区别在于：因为它比较松散，并且因为实现者没有忠实的参考（6NF），所以他们仅实现所需的东西，并全部用代码编写；最终导致模型不一致。
   。
   鉴于纯6NF实现确实具有纯学术参考点，因此通常更加严格且一致。通常，这显示在两个可见元素中：

   • 6NF有一个包含元数据的目录，并且所有内容都是在元数据中定义的，而不是代码。EAV没有一个，一切都在代码中（实现者跟踪对象和属性）。显然，目录使添加列，导航变得容易，并允许形成实用程序。
   • 当理解6NF时，它可以真正解决Null问题。EAV实现者由于缺少6NF上下文，因此会不一致地处理代码中丢失的数据，或者更糟的是，允许数据库中的Null。6NF实现者禁止使用Null，并一致而优雅地处理丢失的数据，而无需代码构造（对于Null处理；当然，您仍然必须为丢失的数据编写代码）。
     。
     例如。对于具有目录的6NF数据库，我有一组proc将[重新生成]执行所有SELECT所需的SQL，并且我为所有用户提供了5NF视图，因此他们不需要了解或理解底层6NF结构。
     。他们被驱逐出目录。因此，更改是容易且自动化的。由于没有目录，EAV类型手动执行此操作。

现在，我们可以开始

讨论区

“如果预先定义了值，那么当然可以更加抽象（例如：专业可以拥有自己的列表）”

当然。但是不要太“抽象”。保持一致性，并以与其他列表相同的EAV（或6NF）方式实施此类列表。

“如果我采用抽象方法，它可能会非常灵活，但是带有许多联接的查询将变得更加复杂。但是，我不知道这是否会影响性能，从而执行这些’更复杂的’查询。”

1. 关系数据库中的联接是行人。问题不在于数据库，问题在于处理联接时，SQL非常麻烦，尤其是复合键。
2. EAV和6NF数据库具有更多的Joins，它们与行人一样多。当然，如果您必须手动编写每个SELECT的代码，那么麻烦就变得很麻烦。
3. 可以通过（a）在EAV上使用6NF以及（b）实施目录来消除整个问题，从中可以（c）生成所有基本SQL。也消除了整个错误类别。
4. 一个普遍的神话是，加入某种方式会产生成本。完全错误。该联接是在编译时实现的，对于“成本” CP​​U周期没有实质性影响。问题是要联接的表的大小，而不是这些相同表之间的联接的成本。在正确的PK⇢FK关系上连接两个表，每个表具有数百万行，每个表具有适当的索引（在parent [FK]侧唯一；在Child侧唯一）。; 如果Child索引不是唯一的，但是至少前导列是有效的，则它慢一些；没有可用索引的地方，那当然很慢。它与加入成本无关。在返回许多行的地方，瓶颈将是网络和磁盘布局。不是加入处理。
5. 因此，您可以随心所欲地获得“复杂”的东西，没有成本，SQL可以处理它。

我想知道这两种方法的优点和缺点。 我可以自己想象，但是我没有经验来确认这一点。

1. 就实施，易用性（开发人员和用户），维护而言，5NF（对于尚未取得进展的人而言，则为3NF）是最简单，最好的。缺点是，每次添加列时，都必须更改数据库结构（表DDL）。在某些情况下很好，但在大多数情况下不是这样，因为适当的变更控制非常繁重。其次，您必须更改现有代码（处理新列的代码不算在内，因为这势在必行）：在实施好的标准的地方，这要最小化；如果没有它们，范围是不可预测的。

2. EAV（这是您发布的内容）允许添加列而无需DDL更改。这就是人们选择它的唯一原因。（处理新列的代码不计算在内，因为这是必须的）。如果实施得当，它将不会影响现有代码；如果没有，它将。但是您需要具有EAV功能的开发人员。当EAV实施不当时，这是可恶的，这比5NF实施得不好更糟，但是却不比未标准化（这是大多数数据库存在的错误）更糟糕（被误称为“性能未标准化”）。当然，拥有强大的Transaction上下文（比5NF / 3NF更为重要），因为列的分布远不止这些。同样，必须保持声明式参照完整性：我所看到的混乱很大程度上归因于开发人员删除了DRI，因为它已成为“

3. 假设已经针对预期目的合理配置了服务器，则性能没有差异。（好吧，只有在6NF中才有可能实现特定的优化，而在其他NF中则无法实现，但是我认为这超出了本线程的范围。）同样，EAV做得不好会造成不必要的瓶颈，仅此而已。未规范化。

4. 当然，如果您使用EAV，我建议您提供更多的手续；买完整的交换；配6NF；实施目录；产生SQL的实用程序；意见；始终处理丢失的数据；完全消除Null。这减少了您对开发人员质量的脆弱性；他们可以忘记EAV / 6NF深奥的问题，使用Views并专注于应用程序逻辑。

请原谅。