选择最好的主键+编号系统 [英] Picking the best primary key + numbering system
问题描述
我们正在试图为我们正在创建的资产系统提出一个编号系统，在办公室里就这一主题进行了热烈的讨论，因此我决定向SO的专家咨询。
考虑数据库设计下面什么是更好的选择。
示例1：使用自动代理键。
================= ================= ====
Road_Number（PK）Segment_Number（PK）
=============== =============== =====
1 1
示例2：使用程序生成的PK
================= ===== =============
Road_Number（PK）Segment_Number（PK）
================= ==== ==============
RD00000001WCK00000001.1
（ 00000001.1 表示它是道路的第一段。每次添加新细分时，都会增加 00000001.2 ）
示例3： ）
===================== == ======================
ID（PK）Road_Number（UK）ID（PK）Segment_Number（UK）
== ===================== ========================
1RD00000001WCK100000001.1
只是一点背景资料，我们将使用道路编号和段号在报表和其他文档中，因此必须唯一 。
我一直喜欢保持简单，所以我更喜欢示例1，但我一直在读，你不应该暴露你的主键在报告/文件。所以现在我更多地沿着例子3的思路。
我也倾向于例3，因为如果我们决定改变我们的资产编号是如何产生的，不必对主键进行级联更新。
您认为我们应该怎么办？
谢谢。
编辑：感谢大家给我的答案，帮助我很多。
这真的是关于代理（也称为技术或综合）vs自然主键的讨论，已经广泛覆盖的主题。我在 AppDevelops制作的数据库开发错误中介绍了这一点。
自然键是基于
的外键有意义的数据，即
（表面上）是唯一的。常见示例
是产品代码，双字母状态
代码（美国），社会保障号
等。代理或技术
主键是
绝对没有意义的在
系统之外。它们是纯粹用于标识实体的
发明的，并且是
通常自动递增字段
（SQL Server，MySQL，其他）或
序列（最明显的是Oracle）。 >
在我看来，您应始终
使用代理键。这个问题有
出现在这些问题：
自动编号字段是要走的路。如果你的键在数据库之外有意义（如资产编号），那么这些键可能会改变和改变键是有问题的。只要在相关的表格中使用这些东西的索引。
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.
Example 1: Using auto surrogate keys.
================= ==================
Road_Number(PK) Segment_Number(PK)
================= ==================
1 1
Example 2: Using program generated PK
================= ==================
Road_Number(PK) Segment_Number(PK)
================= ==================
"RD00000001WCK" "00000001.1"
(the 00000001.1 means it's the first segment of the road. This increases everytime you add a new segment e.g. 00000001.2)
Example 3: Using a bit of both(adding a new column)
======================= ==========================
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.
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.
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.
EDIT: 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.
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屋！
