关系数据库设计问题 - 代理键还是自然键？
哪一种是最佳实践？为什么？
a) 类型表，代理/人工密钥
外键从 user.type 到 type.id： 
b) 类型表，自然键
外键从 user.type 到 类型.类型名称： 
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:
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（10）
代理键是自然主键的替代。
它只是每行的唯一标识符或数字，可用作表的主键。
代理主键的唯一要求是它对于表中的每一行都是唯一的。
它很有用，因为自然主键（即客户表中的客户编号）可以更改，这使得更新更加困难。
Surrogate key is a substitution for the natural primary key.
It is just a unique identifier or number for each row that can be used for the primary key to the table.
The only requirement for a surrogate primary key is that it is unique for each row in the table.
It is useful because the natural primary key (i.e. Customer Number in Customer table) can change and this makes updates more difficult.
我相信在实践中，使用自然密钥很少是最好的选择。我可能会采用 代理键 方法，如第一个示例中所示。
以下是自然键方法的主要缺点：
您可能有不正确的类型名称，或者您可能只是想重命名该类型。要编辑它，您必须更新所有将其用作外键的表。
int字段上的索引比varchar字段上的索引紧凑得多。在某些情况下，可能很难拥有唯一自然键，这是必要的，因为它将用作主键。这可能不适用于您的情况。
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
intfield will be much more compact than one on avarcharfield.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.
第一个更加面向未来，因为它允许您更改表示类型的字符串，而无需更新整个用户表。换句话说，您使用代理键，这是为了灵活性而引入的附加不可变标识符。
The first one is more future proof, because it allows you to change the string representing the type without updating the whole user table. In other words you use a surrogate key, an additional immutable identifier introduced for the sake of flexibility.
使用代理键（而不是像名称这样的自然键）的一个很好的理由是，就唯一性而言，自然键并不是一个很好的选择。在我的一生中，我认识不少于 4 个“克里斯·史密斯”。人名并不唯一。
A good reason to use a surrogate key (instead of a natural key like name) is when the natural key isn't really a good choice in terms of uniqueness. In my lifetime i've known no fewer than 4 "Chris Smith"s. Person names are not unique.
我更喜欢使用代理键。人们通常会识别并使用自然密钥，这在一段时间内会很好，直到他们决定要更改该值。然后问题就开始了。
I prefer to use the surrogate key. It is often people will identity and use the natural key which will be fine for a while, until they decide they want to change the value. Then problems start.
您可能应该始终使用 ID 号（这样，如果您更改类型名称，则无需更新用户表）它还可以让您保持数据大小较小，因为一张充满 INT 的表比一张小得多充满 45 个字符的 varchar。
You should probably always use an ID number (that way if you change the type name, you don't need to update the user table) it also allows you to keep your datasize down, as a table full of INTs is much smaller than one full of 45 character varchars.
如果 typeName 是自然键，那么它可能是更好的选择，因为它不需要连接来获取值。
仅当名称可能更改时才应真正使用代理键 (id)。
If typeName is a natural key, then it's probably the preferable option, because it won't require a join to get the value.
You should only really use a surrogate key (id) when the name is likely to change.
请为我也提供代理密钥。
当您需要敲出一些代码时，另一个可能会更容易，但最终会变得更难。当年，我的技术老板认为使用电子邮件地址作为主键是一个好主意。不用说，当人们想要更改地址时，这真的很糟糕。
Surrogate key for me too, please.
The other might be easier when you need to bang out some code, but it will eventually be harder. Back in the day, my tech boss decided using an email addr as a primary key was a good idea. Needless to say, when people wanted to change their addresses it really sucked.
每当自然键工作时就使用它们。名字通常不起作用。他们太善变了。
如果您正在发明自己的数据，那么您不妨发明一个合成密钥。如果您正在构建其他人或其软件提供的数据数据库，请分析源数据以了解他们如何识别需要识别的事物。
如果他们能够很好地管理数据，他们将拥有适用于重要内容的自然键。对于不重要的事情，适合自己。
Use natural keys whenever they work. Names usually don't work. They are too mutable.
If you are inventing your own data, you might as well invent a syntheic key. If you are building a database of data provided by other people or their software, analyze the source data to see how they identify things that need identification.
If they are managing data at all well, they will have natural keys that work for the important stuff. For the unimportant stuff, suit yourself.
好吧，我认为当您没有任何唯一标识的键（其值与其主键相关且有意义）时，代理键会很有帮助......此外，代理键更容易实现，维护开销也更少。
但另一方面，代理键有时会通过连接表产生额外的成本。
想想“用户”......我有
表结构。
现在考虑一下我想在许多表上跟踪谁在插入记录...如果我使用
Id作为主键，则[1,2,3,4,5 ..]等将在外部表中，每当我需要知道谁在插入数据时，我必须将用户表与其连接起来，因为1,2,3,4,5,6没有意义。但如果我使用UserId作为唯一标识的主键，那么在其他外部表上[john, annie, nadia, linda123]等将被保存，有时很容易区分并且有意义。所以我不需要每次查询时都加入用户表。但请注意，它需要一些额外的物理空间，因为 varchar 保存在外部表中，这需要额外的字节..当然索引有一个显着的性能问题，其中 int 比 varchar 性能更好
well i think surrgote key is helpful when you don't have any uniquely identified key whose value is related and meaningful as is to be its primary key... moreover surrgote key is easier to implement and less overhead to maintain.
but on the other hand surrgote key is sometimes make extra cost by joining tables.
think about 'User' ... I have
as the table structure.
now consider that i want to take a track on many tables as who is inserting records... if i use
Idas a primary key, then[1,2,3,4,5..]etc will be in foreign tables and whenever i need to know who is inserting data i've to join User Table with it because1,2,3,4,5,6is meaningless. but if i useUserIdas a primary key which is uniquely identified then on other foreign tables[john, annie, nadia, linda123]etc will be saved which is sometimes easily distinguishable and meaningful . so i need not to join user table everytime when i do query.but mind it, it takes some extra physical space as varchar is saved in foreign tables which takes extra bytes.. and ofcourse indexing has a significant performance issue where int performs better rather than varchar