主页 / dba / 问题 / 142825
Accepted
Nishant
Asked: 2016-07-03 00:45:28 +0800 CST

关于关系数据库中的查找表的最佳实践是什么？

  • 772

查找表（或代码表，有些人称之为）通常是可以为特定列给出的可能值的集合。

例如，假设我们有一个名为party（用于存储有关政党的信息）的查找表，它有两列：

  • party_code_idn，它保存系统生成的数值，并且（缺乏业务领域含义）用作真实键的代理。
  • party_code, 是表的真实或“自然”键，因为它维护具有业务领域内涵的值。

让我们说这样的表保留了以下数据：

 +----------------+------------+
 | party_code_idn | party_code |
 +----------------+------------+
 |              1 | Republican |
 |              2 | Democratic |
 +----------------+------------+

该party_code列保留值“Republican”和“Democratic”，作为表的真正键，设置了一个 UNIQUE 约束，但我选择添加party_code_idn并将其定义为表的 PK（尽管从逻辑上讲,party_code可以作为 PRIMARY KEY [PK])。

问题

从事务表中指向查找值的最佳实践是什么？我应该建立外键（FK）引用（a）直接指向自然和有意义的值还是（b）替代值？

选项（a），例如，

 +---------------+------------+---------+
 | candidate_idn | party_code |  city   |
 +---------------+------------+---------+
 |             1 | Democratic | Alaska  |
 |             2 | Republican | Memphis |
 +---------------+------------+---------+

具有以下属性1：

  1. 最终用户可读 (+)
  2. 易于跨系统导入导出(+)
  3. 很难更改值，因为它需要在所有引用表中进行修改 (-)
  4. 添加新价值并不昂贵（=）

我认为这几乎就像“按值传递”，以应用程序编程术语中的函数调用进行类比。

选项 (b)，例如，

 +---------------+----------------+---------+
 | candidate_idn | party_code_idn |  city   |
 +---------------+----------------+---------+
 |             1 |              1 | Alaska  |
 |             2 |              2 | Memphis |
 +---------------+----------------+---------+

具有以下属性：

  1. 最终用户不可读 (-)
  2. 难以导入导出，因为我们需要取消引用它 (-)
  3. 易于更改值，因为我们仅将引用存储在事务表中(+)
  4. 添加新价值并不昂贵（=）

如果与应用程序编程用语中的函数调用相比，它与“通过引用传递”非常相似。

导入-导出也可以以不同的方式完成，即，只需再次填充查找表，然后重新播种代理列。我希望我做对了，这是我刚刚听说的一种可能性。

1. 注意+,-并=指出这些属性的好处。

问题

非常重要的是：如果我们只使用后一种方法，查找（或代码）表和 FK 引用之间是否有区别？我认为它们的工作方式相同。

相关资源

database-design foreign-key

3 个回答

  1. 第三种方法具有您的两个选项的一些优点 - 将实际代码放入代码表中。我的意思是一个简短的字符序列，它抓住了全部价值的本质并且是独一无二的。对于您给定的示例，它可能是

    Idn: 1
Name: Democrats
Code: D      (or DEM)

    代码作为外键携带到事务表中。它简短、易懂，并且在某种程度上独立于“真实”数据。对a 名称的增量更改不会暗示代码更改。然而，如果共和党人大规模撤离，则可能需要更改代码，随之而来的问题是代理身份不会产生。

    这种风格被称为缩写编码。我可以推荐 Celko 在这方面的文章。谷歌书籍有几个例子。搜索“Celko 编码”。

    其他示例：国家/地区的 2 或 3 个字母编码，货币代码的 3 个字母编码（GBP、USD、EUR）。简短，不言自明，不改变（并且有一个 ISO）。

    Idn、Code 和 Name 中的每一个都是唯一的，因此每个都是候选键，并且可以选择任何一个作为主键。因此，对于给定的示例，可以从表定义中删除 Idn，并改为使用代码。不同的 DBMS 以自己的方式处理整数和字符串，因此可能存在性能方面的考虑。在某些表中将 Idn 作为 FK 并在其他表中将 Code 作为 FK 可能很有用。

    • 12
  2. Best Answer

    ，IDN我认为你的意思是一个IDENTITY，SEQUENCE或AUTO_INCREMENT领域？你应该看看这里和这里。

    请注意，第 5 节（将数据值用作数据元素）第一个参考，在图 10 下方

    当然，您可以为销售人员创建一个单独的表，然后使用外键引用它，最好使用简单的代理键，例如 sales_person_id ，如上所示。

    所以，这位专家认为你应该“尊重”代理键。这确实是一种非常基本的 SQL 技术，不会在您的日常 SQL 中引起问题。图 10 中似乎存在错误 - SalesData 中的 sales_person 应该是代理键（即数字），而不是文本。我从上面的引用中推断出这一点。

    您应该不惜一切代价避免犯下第 (1) 通用查找表中概述的错误的诱惑（对于新手数据库程序员来说非常常见）。这通常被称为 MUCK（大规模统一代码密钥）方法（不是偶然:-)，尤其是Joe Celko，也被讽刺地称为 OTLT -一个真正的查找表）并导致各种困难。新手程序员似乎觉得单个代码/查找/任何表“更干净”，并且当没有什么比事实更进一步时效率更高。

    从上面的第二个参考：

    规范化消除了冗余数据，从而使强制数据完整性的任务变得非常简单，但是创建 MUCK 的过程完全是另外一回事。MUCK 不会消除冗余数据，而是消除了被认为是冗余表的内容，但是正如我将展示的，更少的表格并不等于简单。

    您可能还想看看我在这里处理的相关 EAV（实体属性值）范例。

    • 11

  3. 答案是，像往常一样，在这种情况下，主要取决于您的价值观是否会发生变化。

    尽管有许多相互竞争的原则，但主要的原则是在关系数据库中，您要不惜一切代价避免更新主键（因此也是外键）值。其他人可能会争辩说，您提出的其他一些因素（例如可读性）是最重要的。但是，我会反驳那些在语义上被破坏的数据对你是否可读没有帮助。

    基于多年的痛苦经验，我使用的经验法则是，任何可能改变的数据元素最终都可能改变。这就是为什么我喜欢几乎每个表都使用无意义的主键的原因。也有例外。例如，如果有一个标准机构（例如 ISO）已经声明了一个查找代码值（例如 USD 代表美元），那么我相信它是稳定的。

    对于您的政党名称的具体用例，我提出加拿大的主流联邦保守党供您参考，该党多年来被称为“进步保守党”，但在与改革合并（接管）后党，他们简要地成为“保守改革联盟党”，然后，在有人费心思考缩写的后果之后，他们选择了“加拿大保守党”。因此，这里有一个具体的例子，说明在查找表中使用代理键可以让您的数据保持准确和一致。

    另一方面（总是至少有一个），如果查找表值不是那么普遍或对您的数据域不是那么重要，那么使用自然键可能值得冒险。所有的设计都是权衡。

    • 2

相关问题