存储查找表id或纯数据之间的决策

您可以使用带有VARCHAR主键的查找表,主数据表的列使用外键,并进行级联更新。

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行表中的主键大小无关紧要。但该查找表可能被数百万或更多用户引用 其他表中的行数!

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

CREATE TABLE Hat (
  hat_id INT NOT NULL PRIMARY KEY,
  brand VARCHAR(255) NOT NULL,
  size INT NOT NULL,
  color VARCHAR(30) NOT NULL /* color is a string, like "Red", "Blue" */
)

或者,您也可以通过制作“颜色”表对其进行更多的规范化:

CREATE TABLE Color (
  color_id INT NOT NULL PRIMARY KEY,
  color_name VARCHAR(30) NOT NULL
)

CREATE TABLE Hat (
  hat_id INT NOT NULL PRIMARY KEY,
  brand VARCHAR(255) NOT NULL,
  size INT NOT NULL,
  color_id INT NOT NULL REFERENCES Color(color_id)
)

后者的最终结果是增加了一些复杂性,而不是:

SELECT * FROM Hat

SELECT * FROM Hat H INNER JOIN Color C ON H.color_id = C.color_id

这是一笔大买卖吗?事实上,这是关系设计模型的基础——规范化允许您防止数据中可能存在的不一致。但每一个这样的情况都会增加一个风险 一点点

• 您是否同时捕获了“颜色名称”和“十六进制值”,使得十六进制值始终依赖于颜色名称?如果是这样,那么您肯定需要一个单独的颜色表,以防止出现一行有(“红色”、“FF0000”)而另一行有(“红色”、“FF3333”)的情况。多个相关属性是一个实体应该规范化的#1信号。
• 使用规范化的查找表将使将来对集合元素的更改更容易,因为您只需更新一行。但是,如果这种情况很少发生,那么不要对必须更新主表中大量行的语句犹豫不决;数据库在这方面做得相当好。如果你不确定,做一些速度测试。
• 这组可能的值是否由用户直接管理?
• 不同值的列表会为某些UI元素提供动力吗? 例如,“颜色”是UI中的滴状列表吗?然后,最好将其放在自己的表中,而不是每次需要显示droplist时都在表上执行SELECT DISTINCT。

如果这些都不适用,我将很难找到另一个(好的)正常化的理由。如果您只是想确保该值是一组特定(小)合法值中的一个,那么最好使用一个约束,该约束表示该值必须位于特定列表中;保持简单,如果需要,您可以随时“升级”到单独的表。

没有人考虑过的一件事是,如果查找表中的数据会随着时间的推移而变化,并且连接到的记录是历史记录,则不会连接到查找表。示例是零件表和订单表。供应商可能会删除零件或更改零件号,但订单表应准确显示订购时订购的内容。因此,它应该查找数据以进行记录插入,但不应该加入查找表以获取有关现有顺序的信息。相反,零件号、说明和价格等应存储在订单表中。这一点尤其重要,以免价格变化通过历史数据传播,使您的财务记录不准确。在这种情况下,您还希望避免使用任何类型的级联更新。

rauhr.myopenid.com wrote :

我们决定用第四范式来解决这个问题。

这不是第四范式。这是一个常见的错误,称为一次真正的查找: http://www.dbazine.com/ofinterest/oi-articles/celko22

第四范式是: http://en.wikipedia.org/wiki/Fourth_normal_form

您甚至可以将始终针对视图编程作为一项规则,让视图获得查找。

在oracle中,如果需要,甚至可以将视图转换为物化视图。