在SQL Server中存储（产品）属性的最佳模式

Question

我们正在启动一个新项目，需要在数据库中存储产品和许多产品属性。技术栈是MS SQL 2008和Entity Framework 4.0 / LINQ用于数据访问。产品（以及产品表）非常简单明了（SKU，制造商，价格等）。然而，还有许多与每个产品一起存储的属性（例如工业小部件）。这些属性可能从颜色到认证（s）到管道尺寸不等。每个产品可能具有不同的属性，并且某些产品可能具有相同属性的多个实例（例如，认证）。目前的建议是，我们基本上将拥有一个名称/值对表格，并在每行中使用带有产品ID的FK。属性表的示例可能如下所示：

ProdID     AttributeName     AttributeValue
123        Color             Blue
123        FittingSize       1.25
123        Certification     AS1111
123        Certification     EE2212
123        Certification     FM.3
456        Pipe              11
678        Color             Red
999        Certification     AE1111
...

注意：属性名称可能来自查找表或枚举。

因此，主要问题是：这是否是实现此类操作的最佳模式？性能如何？查询将基于产品和属性表的连接，并且通常需要许多WHERE子句来过滤特定的属性 - 最常见的搜索是根据一组已知/期望的属性查找产品。

如果有人对此类数据有任何建议或更好的模式，请告诉我。

谢谢！ -Ed

- EdH

Answer 1

您即将重新发明令人望而生畏的EAV模型，即实体-属性-值模型。由于各种原因，该模型在实际应用中存在问题，这些问题已经被Dave的回答所涵盖。

幸运的是，SQL客户咨询团队(SQLCAT)有一篇关于这个主题的白皮书，针对性能和可扩展性的语义数据建模最佳实践。我强烈推荐这篇文章。不幸的是，它没有提供一个万能解决方案，也就是说没有通用的解决方法，因为这个问题本身并没有解决方案。相反，您会学习如何在固定可查询模式和灵活的EAV结构之间找到平衡点，这个平衡点适合您的特定情况：

语义数据模型可以非常复杂，直到语义数据库普及，每个应用程序都需要在纯对象模型和纯关系模型之间找到最佳平衡点。成功的关键在于理解问题，采取必要的减轻措施，并进行测试、测试和测试。如果您要找到最佳设计，则可扩展性测试是关键的成功因素。

- Remus Rusanu

---

+1，仅因为链接的论文比迄今为止在此页面上写的任何内容都更有用。 - Craig Stuntz

Answer 2

这将有几个问题：

• 编写实体查询将更加困难。当到达呈现时间时，将这些查询结果转换为类似ViewModel的内容将很痛苦，因为每个产品都需要一个支点。

• 在阅读某些类型的数据时，确定数据类型将会很困难。您计划将其存储为字符串吗？例如，DateTimes保存的数据比默认的.ToString()实现写入字符串的数据更多。如果尝试存储浮点值，也会出现问题。

• 对象的数据完整性处于风险之中。会有一种诱惑，即将应该是主产品表的属性仅作为"数据桶"的属性。也许最初的设计还算合理，但我保证，经过一定时间后，人们会开始只是将属性扔进袋子里。那时，使用如此松散定义的结构来保持对象的完整性将非常困难。

• 索引可能不够优化。再次考虑应该在产品表上的属性。您现在将被迫在"type"表上创建一个可能非常大的组合索引，而无法只在一个列上进行索引。

• 由于您明显计划放弃正确的数据类型并使用字符串，因此数字数据的范围查询性能可能很差。

• 您的表将变得很大，会减慢备份和查询速度。整数的长度为4个字节，但对于任何大小的整数，您现在都必须存储更多信息。

最好以更加"传统"的方式使用"IS-A"关系对表进行规范化。例如，您可能有一种类型的产品-管道，但它们还具有其他属性。您可能有一种类型的产品-炉子，但它们仍然具有其他属性。如果您的数据库是通用的，以及一些其他属性不会受到数据完整性规则的约束，那么您可能需要考虑在XML列中存储数据。除非我更加了解您的业务，否则很难告诉您正确的设计选择。

在我看来，这是一种设计反模式。这个想法的诱惑力已经引诱了许多开发人员进入无法维护的应用程序的险境。

- Dave Markle

---

感谢您花时间留下详细的回复。我们同意这个计划存在许多问题，但您提出的（半）解决方案也不会有所帮助。单个SKU可能有数百个属性。认证和其他属性每月都在变化。我不确定我们如何能够通过传统的IS-A关系来管理此模式。我们需要多人专门负责此应用程序/模式，以便每周管理SKU更改。 - EdH

这就是为什么你应该考虑使用XML列的原因。你可以在数据库中使用EAV来存储某些属性，但不要期望搜索和报告会快速或直观。你真的需要了解这些属性是否会被搜索，以及每个属性在应用程序生命周期中可能会出现和消失的方式。这种细节的业务分析将告诉你每个属性应该属于哪里。 - Dave Markle

Answer 3

我知道这是一个老问题 - 但可能还有其他读者……

我看到过基于EAV的平衡属性建模方法。好吧，它仍然是EAV。 "EAV就像毒品"这句话几乎是正确的。那么再考虑一下 - 让我们真正积极一些：我仍然喜欢超类型(apporach)，其中很多表使用来自关键生成器的相同主键。让我们重用它。因此，为每组属性创建一个新表怎么样 - 所有这些表都具有相同密钥生成器的主键？例如。您将拥有一个带有字段“color，pipe”​​的表，另一个带有字段“fittingsize，pipe”​​等等。无论如何，“属性易变性”都需要维护精心（自动）的数据字典。

这种方法完全规范化，并且可以完全自动化。您可以支持检查特定属性集是否已经作为表材料化，通过哈希属性名称簇，例如crc32(lower（'color〜fittingsize〜pipe'）))，其中属性名称需要按字母顺序排序。当然，这需要在数据字典中有哈希。根据数据字典，可以通过“UNION”搜索每个对象，特别是如果数据字典本身是一个表。将数据字典作为表也允许您使用其主（代理）键作为唯一表名的基础，以最终获得类似“attributes1”，“attributes2”等的表。大多数数据库现在支持几十亿个表 - 因此我们在这方面也比较安全。您甚至可以有一个非常常见的属性产品目录，该目录引用扩展属性表。

一个未决问题是1：n数据集。恐怕您需要将它们分类到单独的表中。但是这非常取决于您的数据表示和查询策略。它们是否应始终作为附加到产品的逗号分隔字符串呈现，还是您希望例如能够查询某个认证的所有产品？

在您抨击此方法之前，请考虑以下问题：它适用于属性易变性 - 数量和质量 - 非常高的用例。还预设了解决方案创建时无法知道大多数属性的情况。因此，请勿在您可以预先模拟属性的上下文中讨论此问题，这将使您能够更好地平衡权衡。

- Steffen

Answer 4

简而言之，您不能只采用一种方法。如果您像示例中使用EAV，那么您将面临诸如其他帖子所述的问题，其中最为严重的将是性能和数据完整性等问题。请让我再次强调，当您进行报告和分析时，将EAV用作解决方案的核心将会失败。但是，正如您所说，您可能有数百个经常更改的属性。

在我看来，解决方案是混合使用。对于常见属性，请使用列/标准架构。对于附加的任意属性，请使用EAV。然而，EAV数据的规则是，您永远不可以，在任何情况下，编写包括属性排序或过滤器的查询。也就是说，您永远不可以编写"Where AttributeName = 'Foo'"这样的查询。架构中的EAV部分表示仅用于跟踪目的的数据集。事实上，我看到许多人通过使用XML实现EAV部分来实现此解决方案。一旦有人想要搜索、过滤、排序或将EAV值放置在报表的特定位置，该属性必须被提升为产品表中的顶级列。

这种混合方法的关键在于纪律。似乎很容易向开发团队添加筛选器、排序或将属性放置在报表的特定位置上，特别是当您受到管理层的压力时。您必须抵制这种诱惑。一旦您走上黑暗之路......如果您认为自己无法保持开发团队的纪律水平，那么我不建议使用EAV。正如我之前提到的，EAV就像药品：在小剂量和正确情况下使用，它们可以产生益处。而一旦过量使用，将会危及生命。

- Thomas

---

我很好奇，为什么我们绝不能在EAV属性上进行过滤/排序？ - MarredCheese

因为与真实表相比，过滤和排序的成本较高。每次都需要有效地重建模式。例如，想象一下我们针对普通表的筛选器是 Where AttribA = X and AttribB = Y and AttribC <= AttribD。针对 EAV 结构编译该筛选器是非常困难且昂贵的。同样，想象一下按 AttribA Asc, AttribB Desc 进行排序。要在 EAV 结构上执行此操作，您最终必须重新生成列结构，以便可以进行筛选或排序。 - Thomas

Answer 5

不要使用名称-值表，而是创建通常的产品表结构，包含所有共同属性，并添加一个XML列来存储因产品而异的属性。

我以前使用过这种结构，效果非常好。

正如@Dave Markle所提到的，名称-值方法可能会导致很多问题。

- Mitch Wheat

---

查询特定属性的 XML 的效率会有多高？ - EdH

如果您明智地创建XML索引，性能将会很好。 - Mitch Wheat