MySQL表设计和规范化协助

Question

注意:此问题已于 2012 年 11 月 19 日改写以进行澄清。我通常在这里没有太多问题，但在为客户站点设计新产品系统时遇到了困难。我们提供一套产品，每个客户都可以卖给他的客户。我们可能会随时添加新产品，但它们都遵循以下格式:

1. 类别
2. 类型
3. 产品

使用之前的结构举一个真实世界的例子:

• 棒球装备
  • 手套
    • 罗林斯
    • 耐克
    • 美津浓
  • bat
    • 伊斯顿
    • 路易斯维尔强击手
• 足球装备
  • 鞋子
    • 耐克
    • 锐步
    • 阿迪达斯
  • 足球
    • 耐克
    • 种苗
    • 威尔逊
....

上面的列表显然还在继续，而且可能要大得多，但它给出了总体思路。

目前，我将特定客户可以销售的产品类型存储在一个平面格式表中，如下所示:

ID  | clientID | categoryID | typeID | productID | customURL
=============================================================
1   |  111     |    1       |   1    |   1       | 1111
2   |  111     |    1       |   2    |   2       | 2222
3   |  111     |    1       |   2    |   3       | 3333
4   |  111     |    2       |   3    |   4       | 4444
5   |  222     |    1       |   1    |   1       | 5555
6   |  222     |    2       |   3    |   4       | 6666

• 在上面的示例中，类别 1 可以是“棒球装备”，类别 2 可以是“足球装备”
• 将对应的categoryID、typeID、productID的名称分别存储在3个FK关系的独立表(innodb)中，以保持规范化。
• 类型指的是二级商品(手套、球棒、鞋子、足球等)。这些数字永远不会相交(这意味着即使一般产品相同，也永远不会有相同的 typeID(棒球鞋与足球鞋有不同的 ID)。
• 在此表中，clientID 1 可以销售 4 种产品，其中 3 种属于类别 1，1 种属于类别 2。ClientID 2 可以销售 2 种产品，每个类别一种。

我倾向于保持表格的结构，但我知道在其他设计中我可能出于规范化目的将表格分开我不确定是否适用于此。如果我将它们分解，我会看到从一张 table 变成 4 张或更多 table ，如下所示:

productsOffered表

ID  | clientID | productID | customURL
======================================
1   |  111     | 1       | 1111
2   |  111     | 2       | 2222
3   |  111     | 3       | 3333
4   |  111     | 4       | 4444
5   |  222     | 1       | 5555
6   |  222     | 4       | 6666

产品定义表

ID  | productID | typeID | productName
======================================
1   |  1        |    1   | rawlings glove
2   |  2        |    2   | product2
3   |  3        |    2   | product3
4   |  4        |    3   | product4

类型定义表

ID  | typeID | categoryID | typeName
=====================================
1   |  1     |    1       | Gloves
2   |  2     |    1       | Bats
3   |  3     |    2       | Shoes
4   |  4     |    2       | Footballs

类别定义表

ID  | categoryID | catName
=============================
1   |  1         | Baseball Equipment
2   |  2         | Football Equipment

我是不是想多了？这两种方法得到最终解决方案的方式不一样吗(后者只是涉及几个连接来收集平面表，如图 1 所示)？

Answer 1

规范化的目的和好处是它使输入异常数据变得更加困难(理想情况下，不可能)。

例如，在您的图 1 中，如何防止您意外存储 typeid 为 3 和 categoryid 为 1 的行？没什么，除了编写绝对完美的应用程序代码。

但是，如果您使用单表方法，并且必须更改 typeid 3 的父类别，则必须更改一百万个位置的数据以反射(reflect)更改。这意味着在执行清理时锁定表，否则可能会同时插入新数据。

规范化有助于消除冗余存储信息，如果每个离散事实(例如 typeid 3 属于 categoryid 2)只存储一次，那么很容易以原子方式进行更改，并且自动更改对该行的所有引用的含义.

你是对的，需要更多的连接——但前提是你像现在一样在所有地方使用伪键。您不一定需要这样做，您可以改用自然键，并且将使用级联外键声明对它们的引用，因此查找表中的更改也会自动更新引用表。

当然，规范化 的规则并不强制要求使用伪 key 。这些规则只字未提。

---

关于您的评论:一个伪 key ，或surrogate key , 是用于标识行的“id”列。通常，这些值是通过自动递增机制分配的，该机制可确保唯一性，同时允许并发事务插入行。 id 的值对其标识的行没有意义。

---

下面显示了您的表在正常形式下的样子，但没有代理键。

productsOffered表

client | product        | customURL
===================================
Smith  | Rawlings Glove | 1111
Smith  | Product 2      | 2222
Smith  | Product 3      | 3333
Smith  | Product 4      | 4444
Jones  | Rawlings Glove | 5555
Jones  | Product 4      | 6666

产品定义表

product        | type
=======================
Rawlings Glove | Gloves
Product 2      | Bats
Product 3      | Bats
Product 4      | Shoes

类型定义表

type      | category
==============================
Gloves    | Baseball Equipment
Bats      | Baseball Equipment
Shoes     | Football Equipment
Footballs | Football Equipment

类别定义表

category
==================
Baseball Equipment
Football Equipment

使用非整数作为主键列的数据类型完全符合关系数据库设计和规范化，因此外键从其他表引用它们。

有充分的理由使用代理键，以提高性能或简洁性或允许其他列中的值自由更改。但是规范化并不强制要求使用代理键。