这个问题（不同类别的不同属性）的最佳做法是什么？

Question

我有一些产品属于同一类别。

每个类别可以拥有不同的属性。

例如，

• 类别汽车具有属性颜色、功率等。
• 类别宠物具有属性体重、年龄等。

类别数量约为10-15个。 每个类别中的属性数量为3-15。 产品数量非常大。

此应用程序的主要需求是非常好的搜索功能。我们将选择类别，并输入此类别中每个属性的条件。

必须为这种情况设计数据库。（SQL Server 2005）

- ri2008xyz

Answer 1

传统的设计方法如下（星号表示主键列）：

Product
  ProductId*
  CategoryId: FK to Category.CategroyId
  Name

Category
  CategoryId*
  Name

Property
  PropertyId*
  Name
  Type

CategoryProperty
  CategoryId*: FK to Category.CategoryId
  PropertyId*: FK to Property.PropertyId

ProductProperty
  ProductId*: FK to Product.ProductId
  PropertyId*: FK to Property.PropertyId
  ValueAsString

如果您可以接受每个属性值都将作为字符串传递到数据库，并且类型转换信息存储在 Property 表中的事实，那么这种布局就足够了。

查询大致如下：

SELECT
   Product.ProductId,
   Product.Name AS ProductName,
   Category.CategoryId,
   Category.Name AS CategoryName,
   Property.PropertyId,
   Property.Name AS PropertyName,
   Property.Type AS PropertyType,
   ProductProperty.ValueAsString
FROM
   Product 
   INNER JOIN Category         ON Category.CategoryId = Product.CategoryId
   INENR JOIN CategoryProperty ON CategoryProperty.CategoryId = Category.CategoryId
   INNER JOIN Property         ON Property.PropertyId = CategoryProperty.PropertyId
   INNER JOIN ProductProperty  ON ProductProperty.PropertyId = Property.PropertyId
                                  AND ProductProperty.ProductId = Product.ProductId
WHERE
   Product.ProductId = 1

提供更多的WHERE条件（使用AND连接），查询速度会更快，但前提是你已经对表进行了正确的索引。

然而，在全文索引的情况下，这种解决方案并不理想。可以通过另外一张表以一种更非规范化的方式存储与ProductId相关联的所有文本来解决这个问题。这个表需要通过监听ProductProperty表中的变化触发器来更新。

- Tomalak

Answer 2

如果应用程序的用户必须在搜索之前选择类别，我建议按类别将产品分成不同的数据库表。这个解决方案也得益于类别本身没有太多共同点的事实。按照类别进行分类还将使每次搜索更快，因为当用户寻找宠物时，不会浪费时间在汽车上。

一旦您将产品分成不同的类别，就可以使用每个类别中产品的公共属性轻松创建表格。您的应用程序的用户界面应该是动态的（我想到一个网络表单），即当用户选择一个类别时，用户可以选择的属性应该发生变化。

请注意，如果您有希望列在多个类别中的产品，则此解决方案将导致表格中的重复数据。在设计数据库时，速度和规范化之间存在权衡。如果您没有适合多个类别的产品，那么我认为这将是最快的解决方案（从搜索速度角度来看）。

- Bill the Lizard

---

哎呀，我得不同意这个观点。每当你需要整合包括分类分配信息在内的产品数据时，查询就必须涉及到10-15个表的连接，还有其他所有的支持表。当然，可以通过为报告单独创建一个模式来减少这个问题，但Tomalak的答案更具扩展性，并且可以轻松支持报告的整合。 - Cory House

OP表示：“这个应用程序的主要要求是非常好的搜索。” 如果您优化后端报告，那么是否忽略了这个要求？ - Bill the Lizard

Answer 3

大多数人建议使用实体-属性-值（EAV）设计的变体。但是这种设计对于您的情况来说过于复杂，会引入很多问题，例如：

• 您无法为属性定义数据类型；您可以将“香蕉”输入整数属性
• 您无法声明属性为强制性的（即在传统表中的NOT NULL）
• 您无法在属性上声明外键约束

如果您只有少量类别，最好使用Bogdan Maxim答案中的解决方案A。也就是说，定义一个名为Products的表，其中包含所有类别共同的属性，并为每个类别定义一个额外的表，以存储特定于该类别的属性。

只有当您拥有无限数量的类别或者必须支持每行产品不同的属性集时，EAV才是一个好的解决方案。但是，由于EAV违反了规范化的几条规则，因此您根本没有使用关系数据库。

如果您确实需要如此灵活的数据存储方式，最好将数据存储在XML中。事实上，您可以研究一下RDF和语义Web框架，例如Sesame。

- Bill Karwin

---

当然，对于大多数情况来说，EAV设计是过度复杂的。但是当你确实需要更多的复杂性时，它可以节省时间。 - Bogdan Maxim

当然，所有解决这个问题的方法都会破坏规范化。 - Bogdan Maxim

不，按类别分别建立表格不会影响规范化。 - Bill Karwin

Answer 4

你可以试试这个。我不太确定你问题的具体细节，也许有人可以帮你更好地翻译一下。

5张表，其中3张用于存储数据，2张用于存储数据之间的映射关系。

tProduct 
  productID
  <other product details>

tCategory
  categoryID
  <other category details>

tProperty
  propertyID
  <other property details>

tProductXCategory
  productyID
  categoryID

tCategoryXProperty
  categoryID
  propertyID

您的查询将需要使用映射表连接数据，但这将允许您在类别、属性和产品之间拥有不同的多对多关系。

使用存储过程或参数化查询来提高搜索性能。

- StingyJack

---

为什么你需要tProductXCategory和tCategoryXProperty？ - xsl

在tProductXCategory中，propertyID应该改为productID。 - Ates Goral

谢谢Ates...我打字不是很好。@XSL，X表是关系表。这就是你如何将类别与属性或产品与类别关联起来的方式。 - StingyJack

@Ates：我指的是之前版本的帖子，在那个版本中两个表将包含相同的列。 - xsl

Answer 5

你可能想考虑使用实体-属性-值类型的安排，这样你就可以为每个产品打上任意名称/值对的属性标签。

- Paul Dixon

Answer 6

你可以尝试一些更面向对象的方法。

1. 为产品定义一个基本表

Products(ProductID, CategoryID, <任何其他共同属性>)

2. 定义一个类别表

Categories(CategoryID, Name, Description, ..)

从这里开始，你有很多选择，几乎所有的选择都会破坏数据库的规范化。

解决方案 A.

如果需要添加新产品，将成为维护噩梦。

A1. 为每个类别定义一个单独的表

Cars(CarID, ProductID, ..) Pets(PetID, ProductID, ..)

A2. 基于关系连接表以使用数据

SELECT <fields> FROM Cars INNER JOIN Products ON Cars.ProductID = Products.ProductID

解决方案 B.

对于不同类型的属性（即 int、varchar 等），将成为维护噩梦。

B1. 为属性定义一个表

CategoryProperty (CPID, Name, Type)

B2. 定义一个表来保存类别和属性之间的关联

PropertyAssociation (CPID, PropertyID)

B12. 定义一个表来保存属性（B1和B2的替代方案）

Properties(CategoryID, PropertyID, Name, Type)

B3. 为每种类型的属性（int、double、varchar等）添加值表

PropertyValueInt(ProductID, CPID, PropertyID, Value) - 用于 int 类型 PropertyValueString(ProductID, CPID, PropertyID, Value) - 用于字符串类型 PropertyValueMoney(ProductID, CPID, PropertyID, Value) - 用于货币类型

B4. 连接所有表以检索所需的属性。

通过使用这种方法，您不必在单独的表格中管理所有属性，而是管理它们的值类型。基本上，涉及的所有表都将是查找表。 缺点是，为了检索每个值，您必须为每个值类型进行“Case”操作。

在选择这些方法中，请参考这些文章（这里和这里）。即使它是关于本地化的，这篇论坛帖子也很有趣并与主题相关。

如果你觉得需要，你还可以使用Tomalak的答案添加强类型。

- Bogdan Maxim

---

更正：如果您添加了很多新的产品类别，选项A将会是一个维护噩梦。如果您添加属于现有类别的新产品，则没有问题。而且，“维护噩梦”这个词可能有些言过其实。 - Walter Mitty

我一定错过了一个单词。哦，算了吧。当我写完答案时，问题已经关闭了。 - Bogdan Maxim

Answer 7

如果您想在类别和属性上更加灵活，您应该创建以下表格：

• product: ProductID
• category: CategoryID, ProductID
• property: PropertyID, CategoryID

当您想要在多个产品之间共享一个类别时，您必须为n:m连接创建一个链接表：

• productCategoryPointer: ProdCatID, ProductID, CategoryID.

您将需要在查询中进行一些连接操作，但是通过正确的索引，您应该能够快速查询数据。

- Jan

Answer 8

我最近不得不这样做，我正在使用NHibernate，其中有三个实体

Product Category Option OptionCategory

一个产品有1 *的类别

一个产品有1 *的选项

一个选项有1个OptionCategory

一旦这个设置完成，您就可以使用Nhibernate缓存

谢谢

- Roundcrisis