选择最好的主键 + 编号系统 [英] Picking the best primary key + numbering system
问题描述
我们正在尝试为我们正在创建的资产系统提出一个编号系统，办公室里就这个话题进行了一些激烈的讨论，所以我决定咨询 SO 的专家.
We are trying to come up with a numbering system for the asset system that we are creating, there has been a few heated discussions on this topic in the office so I decided to ask the experts of SO.
考虑下面的数据库设计，什么是更好的选择.
Considering the database design below what would be the better option.
示例 1: 使用自动代理键.
================= ==================
Road_Number(PK) Segment_Number(PK)
================= ==================
1 1
示例 2: 使用程序生成的 PK
Example 2: Using program generated PK
================= ==================
Road_Number(PK) Segment_Number(PK)
================= ==================
"RD00000001WCK" "00000001.1"
(00000001.1 表示它是道路的第一路段.每次添加新路段时增加，例如 00000001.2)
(the 00000001.1 means it's the first segment of the road. This increases everytime you add a new segment e.g. 00000001.2)
示例 3:两者兼用(添加新列)
======================= ==========================
ID(PK) Road_Number(UK) ID(PK) Segment_Number(UK)
======================= ==========================
1 "RD00000001WCK" 1 "00000001.1"
只是一点背景信息，我们将在报告和其他文档中使用道路编号和路段编号，因此它们必须唯一.
Just a bit of background information, we will be using the Road Number and Segment Number in reports and other documents, so they have to be unique.
我一直喜欢保持简单，所以我更喜欢示例 1，但我一直在读到你不应该在报告/文档中公开你的主键.所以现在我更多地按照示例 3 的思路思考.
I have always liked keeping things simple so I prefer example 1, but I have been reading that you should not expose your primary keys in reports/documents. So now I'm thinking more along the lines of example 3.
我也倾向于示例 3，因为如果我们决定更改资产编号的生成方式，则不必对主键进行级联更新.
I am also leaning towards example 3 because if we decide to change how our asset numbering is generated it won't have to do cascade updates on a primary key.
你认为我们应该怎么做?
What do you think we should do?
谢谢.
感谢大家的精彩回答，对我帮助很大.
Thanks everyone for the great answers, has help me a lot.
推荐答案
这实际上是关于代理(也称为技术或合成)与自然主键的讨论，该主题已被广泛涵盖.我在应用程序开发人员犯下的数据库开发错误中对此进行了介绍.
This is really a discussion about surrogate (also called technical or synthetic) vs natural primary keys, a subject that has been extensively covered. I covered this in Database Development Mistakes Made by AppDevelopers.
自然键是基于外部有意义的数据(表面上)独一无二的.常见例子是产品代码，两个字母的状态代码(美国)，社会安全号码等等.代理或技术主键是那些具有绝对没有意义之外系统.它们的发明纯粹是为了识别实体并且是通常自动递增的字段(SQL Server、MySQL 等)或序列(最著名的是 Oracle).
Natural keys are keys based on externally meaningful data that is (ostensibly) unique. Common examples are product codes, two-letter state codes (US), social security numbers and so on. Surrogate or technical primary keys are those that have absolutely no meaning outside the system. They are invented purely for identifying the entity and are typically auto-incrementing fields (SQL Server, MySQL, others) or sequences (most notably Oracle).
我认为你应该总是使用代理键.这个问题有提出这些问题:
In my opinion you should always use surrogate keys. This issue has come up in these questions:
自动编号字段是要走的路.如果您的密钥在您的数据库之外具有意义(例如资产编号)，那么这些很可能会发生变化，并且更改密钥是有问题的.只需将这些内容的索引放入相关表中即可.
Auto number fields are the way to go. If your keys have meaning outside your database (like asset numbers) those will quite possibly change and changing keys is problematic. Just use indexes for those things into the relevant tables.
这篇关于选择最好的主键 + 编号系统的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
