为主键使用特定于项目的前缀和自动编号? [英] 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 - 字符串 - 主键
- 类型 - 字符串
所以一些示例数据是这样的:
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?
是的，我将在方法 1 中使用 AUTO_INCREMENT.
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屋！
