使用项目特定的前缀和主键的自动编号？ [英] Use item specific prefixes and autonumber for primary keys?

问题描述

我们今天上午有一次会议，应该如何存储我们在我们所在数据库中的一些资产的ID，这种描述产生了一些热，所以我决定咨询SO的专家。

We had a meeting this morning about how would should store our ID for some assets that we have in our database that we are making, the descusion generated a bit of heat so I decided to consult the experts of SO.

我相信我们应该拥有的表结构（简称）如下：

The table structure that I belive that we should have(short version) is like the following:

示例1）

• AssetId - int（32） - 主键

<字符串

所以一些示例数据是这样的：

so some example data is like this:

==AssetId======Type===
  12345        "Manhole"
  155415       "Pit"

等。

团队的另一名成员建议如下：

Another member of the team suggested something like this:

示例2）

• AssetId - string - 主键


• 类型 - 字符串

所以一些示例数据是这样的：

so some example data is like this:

==AssetId======Type===
  "MH12345"    "Manhole"
  "P155415"    "Pit"

我们制作一个简短版本的类型，并将其附加到ID的前端，并将其存储在数据库中。我看到一些资产数据库这样做，从来没有这样做。

where we make a short version of the type and append it to the front of the ID and store it in the database. I have seen a few asset databases that do this and have never really this approach.

我从来没有真正喜欢使用字符串作为ID进行排序的原因。当您已经拥有资产商店的类型时，我也觉得它正在存储无用的信息。

I have never really liked the idea of using strings as ID for sorting reasons. I also feel like it is storing useless information just for the sake of it when you already have the type of asset store anyway.

你会采取什么方法？为什么？使用方法1超过2有什么好处吗？

What approach would you take? And why? Are there any benefits to using approach 1 over 2?

编辑：是的，我将使用AUTO_INCREMENT方法1。

Yes I will be using AUTO_INCREMENT for approach 1.

推荐答案

通常的经验法则是在主键（如社会安全号码或条形码）中从不使用有意义的信息。只是简单的自动增量整数。
然而数据似乎不变 - 它可能会改变一点（新立法来重新计算所有SSN）。

Usually the rule of thumb is that never use meaningful information in primary keys (like Social Security number or barcode). Just plain autoincremented integer. However constant the data seems - it may change at one point (new legislation comes and all SSNs are recalculated).

这篇关于使用项目特定的前缀和主键的自动编号？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！