关系数据库设计问题 - 代理键还是自然键? [英] Relational database design question - Surrogate-key or Natural-key?

问题描述

哪个是最佳实践，为什么?

外键是从 user.type 到 type.id:

外键是从 user.type 到 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.

int 字段上的索引将比 varchar 字段上的索引紧凑得多.

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.

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