sql - 在 RDBMS 中实现灵活的关系——真正的权衡是什么？

Question

我有一堆产品，每个产品都有一堆不同的可能属性。例如。产品 A 有名称、尺寸、颜色、形状。产品 B 有名称、卡路里、糖等。解决这个问题的一种方法是:

1) 创建表

Products (id, name)
Attributes (id, name)
Product_Attributes (product_id, attribute_id, value as string)

这允许最大的灵活性，但我听说很多人反对这样做，尽管我不确定为什么。我的意思是，如果这些表被称为 Teams、Players、Team_Players，我们都会同意这是正确的关系设计。

每个向我解释为什么这很糟糕的人都是在完全灵活的关系设计的上下文中这样做的，在这种设计中，您永远不会通过一些基本的初始表(例如对象、属性、对象属性)创建真正的表——我认为我们所有人都同意是不好的。但这是一个更加有限和包含的版本(只有产品，而不是系统中的每个对象)，所以我认为将这两种架构组合在一起是不公平的。

您遇到了哪些问题(经验或理论)使这个设计如此糟糕？

2) 解决这个问题的另一种方法是创建一个 Product 表，其中包含一堆列，如 Size、Color、Shape、Weight、Sugar 等，然后在最后包含一些额外的列，为我们提供一些灵活性。这将创建主要填充 NULL 的通常稀疏的行。人们倾向于喜欢这种方法，但我的问题是，在这种方法失去性能优势之前，您可以拥有多少列？如果您有 200 列，我想这不再是明智之举，但是 100 列呢？ 50 列？ 25 列？

3)我知道的最后一种方法是将所有属性作为 blob(可能是 JSON)存储在 Products 表的单个列中。我喜欢这种方法，但感觉不对。查询很困难。如果您希望以后能够轻松更改属性的名称，您要么必须单独解析每条记录，要么让它们通过某个 id 键入您的 blob。如果你走 id 路径，那么你将需要另一个表 Attributes 并且事情开始看起来像上面的方法 #1 除非你将无法将 attribute_id 与你的 blob 连接起来，所以我希望你不想查询任何东西按属性名称。

我喜欢这种方法的一点是，您可以查询一个产品，并且在您的代码中，您可以轻松访问它拥有的所有属性——快速。如果您删除一个产品，您将不必清理其他表——易于保持一致。

4) 我已经阅读了一些关于能够在某些 RDBMS 中索引强类型 xml 格式的内容，但老实说，我对这种方法不太了解。

我被困住了。我觉得方法#1 是最好的选择，但我读到的所有内容都这么说很糟糕。考虑这个问题的正确方法是什么，以便能够决定在给定情况下最好的方法是什么？显然欢迎比我列出的更多想法!

Answer 1

通过对“实体属性值反模式”进行 Google 搜索，您可能会找到有关此主题的大量信息。

这种方法的问题之一是您最终将元数据与实际数据混合在一起。您的“属性”现在必须告诉数据库“值”列中究竟保存了什么。这会使在前端、报告软件等中处理这些数据变得非常困难。

其次，您将很难在数据库中实际执行任何数据完整性。当您的产品具有“重量”属性时，如何阻止某人将“22 英寸”放入值中？或者完全是一个非数字值。你可能会说，“好吧，我的应用程序会处理这个。”然后每次要添加新属性时都需要更改应用程序，因为应用程序需要知道如何处理它。如果您要完成所有这些工作，只需添加一个新列。

第三，您如何确保给定的产品具有它需要的所有属性？在一行中，您可以将列设为 NOT NULL，然后他们需要将该行放入数据库。你不能在 EAV 模型中强制执行。

第四，这种模型通常会导致很多困惑。人们不确定支持哪些“属性”，或者他们复制了一个属性，或者他们在创建报告时忘记处理一个属性。例如，如果我有一个属性“重量(公斤)”和另一个属性“重量(磅)”，有人问我，“你的数据库中最重的产品是什么？”我最好记住我需要检查这两个属性。

第五，这种模式通常也会导致懒惰。嘿，没有理由对我们的系统可以处理的产品进行任何实际分析，因为无论出现什么，我们都会添加一些属性。根据我的经验，公司最好进行创建良好数据库设计所需的分析，而不是依赖于这样的反模式。您将了解有关数据库、应用程序以及可能的业务的知识。

第六，获取给定产品的单行数据可能需要很多连接。您可以将属性作为单独的行返回，但现在您必须想出自定义列表框来列出这些产品等。同样，针对此模型编写搜索查询可能非常困难，在这两种情况下，您都可能有性能问题。

这些只是我多年来遇到的一些问题。我确定还有其他人。

适合您系统的正确解决方案在很大程度上取决于您的业务和应用程序的具体情况。如果您的产品属于几个具有共同属性的类别，您可以考虑使用子类型表而不是稀疏行。