选择最好的主键+编号系统

问题描述

我们正在试图为我们正在创建的资产系统提出一个编号系统，在办公室里就这一主题进行了热烈的讨论，因此我决定向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自然主键的讨论，已经广泛覆盖的主题。我在中介绍了这一点。

自动编号字段是要走的路。如果你的键在数据库之外有意义（如资产编号），那么这些键可能会改变和改变键是有问题的。只要在相关的表格中使用这些东西的索引。

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.

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.

这篇关于选择最好的主键+编号系统的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！