数据库设计：键值对 - 是好还是坏？

Question

假设我有一个在线商店，每个产品都属于一个单一类别（有数百个可供选择的类别），例如“书籍”、“便携式DVD播放器”等。如果我需要为每个类别提供描述性字段（例如，“作者”是“书籍”类别的一个字段），那么在数据库中最好的表示方法是什么？

选项1（名称-值对）：

===========================
 field
===========================
- field_id
- category_id (FK, referring to category like "book")
- name
- value

这意味着我可以依靠一个表格来管理任何类别的数据。但我担心在与其他书籍并排显示时需要进行透视可能会成为一个潜在的问题。

选项2（单独的表格）：

===========================
 book_field
===========================
- book_field_id
- book_id (FK, referring to the actual book)
- author
- title
- publisher
- date_published
...

这意味着我需要为每个类别创建一个表格。

注意：虽然我认为这并不重要，但类别将来自类别层次结构（例如，电子产品-> DVD播放器->便携式DVD播放器）。

- StackOverflowNewbie

Answer 1

我的建议是每个类别使用一个表。如果事物确实不同，请接受这一点并相应地设置表格。

当然，如果某些实体具有共同的数据，则可以将其抽象化/规范化，但我认为您提供的名称/值对选项可能会导致一些可读性/查询性能问题。

- Bob Palmer

---

现实世界中的注释 - 在我的工作中，我们有几种非常相似但具有独特属性的表格 - 我们为每个使用特定的表格，一些共同元素的表格（名称，地址等），以及一些视图，当我们需要将这些对象作为整体查看时，这些视图可以帮助我们聚合它们。 - Bob Palmer

鲍勃说的话。还要搜索“泛化特化关系建模”。 - Walter Mitty

同意。名称-值对系统使得返回一个每个类别都有一列的简单数据表变得困难（例如BookID，Author，Title等）。 - Vince Bowdren

您建议为每个类别创建一个表。如果需要添加新的类别，那么您是否需要为其创建一个新表？在生产环境中，我宁愿将一行添加到现有表中，而不是创建一个新表。在对象关系映射框架（如Rails）中，情况会变得棘手。 - hade

具有讽刺意味的是，我们现在正在生产环境中进行此操作（新医院表单）。通常添加新实体还包括滚动新的UI组件，构建新的ORM映射等等。无论如何，将新表格、新UI插入并连接好这些好东西只是我们用户故事的一部分 :) - Bob Palmer

Answer 2

你确定只想限制到一个类别吗？我的意思是，你能想到任何情况下你的产品可能属于多个类别吗？

无论如何，这里有一个可能对你有用的解决方案：

更新（添加了几个层）

========
products
========
- product_id
- name

====================
categories_products
====================
- category_product_id
- product_id (FK)
- category_id (FK)

===========
categories
===========
- category_id
- name

=============================
products_detail_values_types
=============================
- product_detail_value_type_id
- product_id (FK)
- detail_value_type_id (FK)

====================
detail_values_types
====================
- detail_value_type_id
- detail_value_id (FK)
- detail_type_id (FK)

===============
detail_values
===============
- detail_value_id
- value

=============
detail_types
=============
- detail_type_id
- name

你有一个名为“director”的类型：

 detail_type:
   detail_type_id: 100
   name: "director"

还有一些值：

detail_value:
  detail_value_id: 200
  value: "James Cameron"      

类型和值的映射：

detail_value_type:
  detail_value_type_id: 300
  detail_value_id: 200
  detail_type_id: 100

哪些细节属于产品：

product_detail_value_type:
  product_detail_value_type: 400
  product_id: 500
  detail_value_type_id: 300

然后我们有分类：

category:
  category_id: 600
  name: "movie"

以及类别-产品映射：

category_product:
  category_product_id: 700
  product_id: 500
  category_id: 600

最后是产品本身：

product:
  product_id: 500
  name: "Aliens"

- hade

---

@hade，现在你让我有点担心。一个产品真的可以属于两个类别吗？我一直在研究亚马逊；不确定是否遇到过这种情况。我错了吗？ - StackOverflowNewbie

@hade，这个设计使我能够拥有不止一个“詹姆斯·卡梅隆”。这是否已经规范化了？ - StackOverflowNewbie

@StackOverflowNewbie：假设你有一个电影类别。你可以有不同格式的电影：VHS、DVD、蓝光。你想如何对它们进行分类？它们都是电影，但也可以根据媒体类型进行分类。假设你想搜索蓝光格式的电影。你可以通过在电影类别和蓝光类别中搜索产品来轻松实现这一点。 - hade

是的，在这个设计中，你可以有多个“詹姆斯·卡梅隆”。这只是一个相当简单的例子。实际上，电影可能有几个导演，比如科恩兄弟。你实际上需要一个单独的人员表和细节表与人员表之间的多对多关系。我想知道在此之后这个答案是否还算简单。 - hade

@hade，在你的例子中，也许“电影”是类别，“蓝光”是细节（就像其他细节一样：导演，类型等）。你觉得呢？ - StackOverflowNewbie

@StackOverflowNewbie：我已经更新了答案。我添加了一些层，使其更加灵活。使用这种设计，您的产品可以属于多个类别。您可以将详细类型和值映射在一起，并将它们用于许多产品。无论您想将蓝光作为类别还是详细信息（如媒体类型），都由您决定 :)。 - hade

Answer 3

我建议您基于互联网标签的设计进行设计。

让我给您解释一下：

除了主对象表外，您将需要4个表。

第一个表是名称标签表，这是一个基本表id | name，将存储对象的属性："author"、"size"、"weight"等任何可以描述对象的属性。

tag_table
id    varchar(36)
tag   varchar(36)

第二条：这个表格将会把数值与存储在tag_table值中的标签名进行匹配。它的设计是相同的。

value_table
id    varchar(36)
value varchar(36)

第三个：将确定哪个值是哪个标签。

tag_value
id_pair  varchar(36)
id_tag   varchar(36)
id_value varchar(36)

第四个：将一个对象与描述它的数据连接起来

object_tag_value
id_object  varchar(36)
id_pair    varchar(36)

最后是您的对象表。

实现层次结构系统：

对于一对多或多对多的层次结构，实现一个额外的表格来关联这两个对象：

object_relation
id_parent varchar(36)
id_son    varchar(36)

对于一对多关系（例如，Employee表带有manager_id），只需将id_parent作为对象成员添加即可。

使用此模式，您将具有高度可扩展性，一个对象现在可以拥有无限的特征，您不再受限制。此外，由于标签名称是唯一的，因此避免了数据冗余。

希望我表述清楚并能帮到您，

- Spredzy

---

关于层次结构怎么样？你的解决方案中没有层次结构的概念，对吗？ - StackOverflowNewbie

有两个层次结构的概念，即“一对多”和“多对多”。如果您需要“一对多”，只需在对象表中添加一个parentId即可。如果是“多对多”，则添加一个具有两个varchar（36）字段的层次结构表，该表将是另一个对象之间的关系，您可以通过这种方式拥有无限的层次结构。 - Spredzy

如果我只需要一对多的层次结构，我是否仍然应该采用您提出的解决方案？ - StackOverflowNewbie

我修改了原帖并写了一些关于层次结构的内容，是的，它与你的解决方案兼容。 - Spredzy