sql - 存储查找表 ID 或纯数据之间的决定

Question

我发现这种情况经常出现，但我不确定处理它的最佳方式。

我的问题是如何决定是使用外键查找表，还是直接在请求它的表中使用查找表值，从而完全避免查找表关系。

注意事项:

• 用第二种方法你会需要对所有人进行大量更新引用数据的记录，如果它在查找表中更改。

• 这样更专注对有很多的表该列引用了许多查找表。因此很多外国 key 意味着很多每次查询时加入表。

• 此数据将来自 drop下拉列表从查找表。为了在重新加载时匹配数据，值需要在现有列表中(与第一点相关)。

此处是否有最佳实践或需要考虑的任何关键点？

Answer 1

您可以使用具有 VARCHAR 主键的查找表，并且您的主数据表在其列上使用 FOREIGN KEY，并进行级联更新。

CREATE TABLE ColorLookup (
  color VARCHAR(20) PRIMARY KEY
);

CREATE TABLE ItemsWithColors (
  ...other columns...,
  color VARCHAR(20),
  FOREIGN KEY (color) REFERENCES ColorLookup(color)
    ON UPDATE CASCADE ON DELETE SET NULL
);

该方案具有以下优点:

• 您可以在主数据表中查询颜色名称，而无需连接到查找表。
• 不过，颜色名称仅限于查找表中的颜色集。
• 您可以通过查询查找表获得唯一颜色名称的列表(即使当前没有在主数据中使用)。
• 如果您更改查找表中的颜色，更改会自动级联到主数据表中的所有引用行。

---

让我感到惊讶的是，这个线程上有这么多其他人似乎对“规范化”是什么有错误的想法。使用代理键(无处不在的“id”)与规范化无关!

---

来自@MacGruber 的评论:

是的，大小是一个因素。例如，在 InnoDB 中，每个二级索引都存储给定索引值出现的行的主键值。因此，您拥有的二级索引越多，为主键使用“庞大”数据类型的开销就越大。

这也会影响外键；外键列必须与其引用的主键具有相同的数据类型。您可能有一个小查找表，因此您认为 50 行表中的主键大小无关紧要。但是该查找表可能会被其他表中的数百万或 数十亿 行引用!

没有适合所有情况的正确答案。对于不同的情况，任何答案都可能是正确的。您只需了解权衡取舍，并尝试根据具体情况做出明智的决定。