关系数据库设计问题 - 代理键或自然键？

问题描述

a）类型表，代理/人造密钥

外键来自 user.type 至 type.id ：

b）类型表，自然键

外键来自 user.type 至 type.typeName ：

解决方案

我相信在实践中，使用是很少是最好的选择。我可能会像你第一个例子中的方法一样。

以下是自然键方法的主要缺点：

• 您可能有一个不正确的类型名称，或者您可能只想重命名该类型。要编辑它，您将不得不更新将作为外键使用的所有表。




• 上的索引int 字段将比 varchar 字段之间更紧凑。




• 在某些情况下，可能难以拥有自然键，这是必要的，因为它将被用作主键。这可能不适用于您的情况。

Which one is the best practice and Why?

a) Type Table, Surrogate/Artificial Key

Foreign key is from user.type to type.id:

b) Type Table, Natural Key

Foreign key is from user.type to type.typeName:

解决方案

I believe that in practice, using a natural key is rarely the best option. I would probably go for the surrogate key approach as in your first example.

The following are the main disadvantages of the natural key approach:

• You might have an incorrect type name, or you may simply want to rename the type. To edit it, you would have to update all the tables that would be using it as a foreign key.

• An index on an int field will be much more compact than one on a varchar field.

• In some cases, it might be difficult to have a unique natural key, and this is necessary since it will be used as a primary key. This might not apply in your case.

这篇关于关系数据库设计问题 - 代理键或自然键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！