存储查找表ID或纯数据之间的决定 [英] Decision between storing lookup table id's or pure data
问题描述
我发现这个问题很多，而且我不确定最好的解决方法.
I find this comes up a lot, and I'm not sure the best way to approach it.
我的问题是如何在使用外键查找表还是直接在请求表中使用查找表值之间做出决定，从而完全避免查找表之间的关系.
要记住的要点:
-
使用第二种方法，您将 需要对所有人进行批量更新 记录引用数据(如果有) 在查询表中更改.
With the second method you would need to do mass updates to all records referencing the data if it is changed in the lookup table.
此重点更加突出 对具有很多 该列引用了许多查询 表.因此很多外国 键意味着很多 每次您查询 表.
This is focused more towards tables that have a lot of the column's referencing many lookup tables.Therefore lots of foreign keys means a lot of joins every time you query the table.
这里是否有最佳实践，或者有什么要考虑的重点?
Is there a best practice here, or any key points to consider?
推荐答案
您可以使用带有VARCHAR主键的查找表，主数据表的列上使用FOREIGN KEY，并具有级联更新.
You can use a lookup table with a VARCHAR primary key, and your main data table uses a FOREIGN KEY on its column, with cascading updates.
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
);
此解决方案具有以下优点:
This solution has the following advantages:
- 您可以查询主数据表中的颜色名称，而无需连接到查找表.
- 尽管如此，颜色名称仍限于查找表中的颜色集.
- 通过查询表，可以获得唯一颜色名称的列表(即使当前主数据中没有使用).
- 如果您在查找表中更改颜色，则更改会自动级联到主数据表中的所有引用行.
令我感到惊讶的是，这个线程上的许多其他人似乎对什么是规范化"有误解.使用代理键(普遍存在的"id")与规范化无关！
It's surprising to me that so many other people on this thread seem to have mistaken ideas of what "normalization" is. Using a surrogate keys (the ubiquitous "id") has nothing to do with normalization!
@MacGruber的评论:
Re comment from @MacGruber:
是的，大小是一个因素.例如，在InnoDB中，每个辅助索引都存储给定索引值所在行的主键值.因此，拥有的二级索引越多，对主键使用庞大"数据类型的开销就越大.
Yes, the size is a factor. In InnoDB for example, every secondary index stores the primary key value of the row(s) where a given index value occurs. So the more secondary indexes you have, the greater the overhead for using a "bulky" data type for the primary key.
这也会影响外键；外键列必须与其引用的主键具有相同的数据类型.您可能有一个小的查询表，因此您认为50行表中的主键大小无关紧要.但是该查找表可能被其他表中的数百万或十亿行引用！
Also this affects foreign keys; the foreign key column must be the same data type as the primary key it references. You might have a small lookup table so you think the primary key size in a 50-row table doesn't matter. But that lookup table might be referenced by millions or billions of rows in other tables!
没有所有情况的正确答案.在不同情况下，任何答案都是正确的.您只需了解权衡，然后尝试根据具体情况做出明智的决定.
There's no right answer for all cases. Any answer can be correct for different cases. You just learn about the tradeoffs, and try to make an informed decision on a case by case basis.
这篇关于存储查找表ID或纯数据之间的决定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
