什么时候将重复的数据移动到引用表是有益的？

Question

如果有来自有限集S的值的列，例如S = { alive, dead, unknown }用于persons表中的life_state列，那么添加引用表什么时候是有益的？

Options

• 在life_state列中存储重复的静态数据
  • 简单字符串:应用程序层级别的代码需要存在才能获得可能的数据值，因为SELECT DISTINCT可能缺少一些值。
  • ENUM：如果您想为life_state获取所有可能的数据值，那么您需要从INFORMATION_SCHEMA运行SHOW COLUMN..来获取所有可能的数据值，并在应用层级别解析这些数据，或者在应用层级别有重复的代码。如果需要添加一个新的life_state值，我们需要在修改表的过程中将整个表锁定在ALTER中。如果需要，不能为每个life_state值存储额外的数据。

• 规范化创建一个引用表life_states和一个返回到persons表的链接表。可能的值可以从SELECT name from life_states;导出，后者将返回3行。现在需要额外的联接才能获得任何life_state实例的persons。这不仅速度较慢，而且在应用层级别需要更长的查询/更多代码。如果有必要，任何附加的值都可以很容易地添加，尽管这不是预期的。此外，如果需要将其他数据添加到life_state类型中，则可以轻松添加。

其他考虑事项

• INT/ENUM索引可能会更快/占用更少的空间字符串。

相关讨论

• 创建一个新的数据库表而不是使用枚举数据类型会浪费吗？

Answer 1

什么时候将重复的数据移动到引用表是有益的？

1. 当引用的数据独立于引用数据存在时。在本例中，即使在persons中没有行的情况下，了解可能的生命状态在问题域中是否有用。
2. 当这些值只能从已知的有限集中提取时。这通常被称为“参考数据”。外键约束可以强制执行此操作。
3. 当引用完整性必须在两个(或更多)规范化实体之间保持时。这将适用于未预先知道值的事务性数据。例如，应用程序允许在运行时添加新用户，并且每个表上都有last_updated_userid。同样，外键将强制执行此操作。
4. 当发现唯一依赖于life_state的附加域值时。这是通常的关键依赖关系正常化。
5. 当需要元数据时，例如用于“软删除”的is_active，而不是从系统中完全删除值。

虽然为引用表创建代理键是惯例，但并不需要。对于给定的示例，引用表可以有一个包含三行的七个字符的列。然后，人类可读的值将出现在引用表中。这将避免加入引用表的需要。

Answer 2

这个答案主要集中于ENUM类型的使用，因为它适用于MySQL，因为它似乎是我使用它们的地方。

你已经回答了你自己的问题，在你自己的帖子底部突出显示的帖子--这都解释了这里，即ENUMs是邪恶的:-)！

数据库中的数据应存储在表中--有关数据库管理的数据应存储在系统表(数据字典)中。如果你想知道S在ENUM中是什么，你必须查询系统表。

我的建议是，对每件有超过2个价值的事物使用表格--即便如此，也要看看围绕这一问题的性别辩论。

正如您所指出的线程中所指出的，您可以通过使用VIEWs预连接一些常用的引用表，从联接中取出一些繁重的工作。另外，您的参考表可以包含不确定的描述--这些合同程序员不需要记住任何关于模糊代码的含义的东西！

基本上，我不明白为什么要在数据库中使用ENUM --我已经有一段时间没有编程了，所以我不太熟悉在语言中使用它们的利弊！

Answer 3

为什么你在一个洞里(特定示例)

这里的问题主要是你如何区分“未知”和“已知未知”。如果有人有death_date，

• 当IS NOT NULL你可以确定他们已经死了的时候，你就会知道他们什么时候死了。(假设任何时候的死亡都有一个日期)
• 然而，当IS NULL可以用两种方式之一解释数据时，也就是
  • 你不知道那个人已经死了。
  • 你知道那个人还活着，(即，不是死的)。

这些在语义上是非常不同的。我不知道这些年轻人已经死了，但是传达他们还活着也是不安全的。特别是如果这些数据将用于分配资源来查找它们。

错误的前提

例如，你在这里有很多错误的陈述。

• ENUM不是一个标准特性，它是一个常见的扩展。关于PostgreSQL，
  • “ENUM是重复的数据。”：它不是在实践中，也不应该是。ENUM可以通过指向系统表(目录)的链接来实现，其方式与指向用户表的链接相同，而且对用户来说是完全透明的。例如，在PostgreSQL中，ENUM是四个字节(一个int的大小)。链接到它将只保存3个字节，因为链接的成本在最好的情况下将是另一个字节。
  • “使用SHOW COLUMN获取潜在值”：这也并非总是必需的。使用的值enum获得可能的SELECT是非常自然的。我们不需要模糊的SHOW语法。
  • 使用ENUM的多个表与类型无关。可以使用ALTER TYPE修改类型本身。它将立即添加值，并在使用该类型的所有表中生效。