在存储查找表 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屋！
