选择主键的数据类型时应该考虑什么？ [英] What should I consider when selecting a data type for my primary key?
问题描述
当我创建一个新的数据库表时，我应该考虑哪些因素来选择主键的数据类型？ p>对不起这样做，但是我发现我给的相关问题的答案（你可以查看这个和这个）可以适用于这一个。我重新塑造了他们一些...
你会发现许多处理这个问题的帖子，你会做出的每一个选择都有其利弊。这些参数通常指关系数据库理论和数据库性能。
在这个问题上，我的观点很简单： 代理主键总是工作 ，而 自然键可能永远不会一天工作 ，这有多种原因：字段太短，规则更改等。 / p>
到目前为止，您已经猜到，我基本上是uniqueIdentifier /代理主键组的成员，即使我欣赏和理解诸如在这里介绍，我还在寻找自然键比代理更好的情况...
除此之外，最重要但总是被遗忘的有利于这个基本规则的论据与代码规范化和生产率有关：
每次我创建一个表格时，我将失去时间
- 标识其主键及其物理特征（类型，大小）
- 向团队中的其他开发人员解释我的PK选择？
我的答案不是所有这些问题：
- 当代理选项给我一个防弹解决方案时，我没有时间失去尝试识别最好的自然主键。
- 我不想记住主键我的Table_white是一个10个字符的长字符串，当我编写代码。
- 我不想失去我的时间谈判自然键长度：如果你需要10为什么不您将12 放在安全方面 ？这个 安全的一面 参数真的让我很烦：如果你想保持安全的一面，这意味着你真的不远处不安全的一面！选择代理：它是防弹的！
所以我在过去五年里一直在工作，有一个非常基本的规则：每个表（我们称之为'myTable'）的第一个字段称为'id_MyTable'，它是uniqueIdentifier类型。即使这个表支持多对多的关系，一个字段组合提供了一个非常可接受的主键，我更喜欢创建这个'id_myManyToManyTable'字段为唯一标识符，只是坚持规则，因为，最后，它不会受伤。
主要的优点是，你不必再关心使用的主键和/或外键。一旦你有了表名，你知道PK的名称和类型。一旦您知道在数据模型中实现了哪些链接，您就可以了解表中可用的外键的名称。
如果您还想拥有自然键，我建议您按照标准模型构建，如
Tbl_whatever
id_whatever，唯一标识符，主键
code_whatever，whateverTypeYouWant（whateverLengthYouEstimateTheRightOne），索引
.....
其中id_是主键的前缀，code_用于自然索引字段。有些人会认为code_字段应该被设置为唯一的。这是真的，它可以通过DDL或外部代码轻松管理。请注意，计算许多自然密钥（发票编号），因此已经通过代码生成了
我不知道我的规则是最好的。但它是一个非常有效的一个！如果每个人都在申请，我们会避免时间的迷失，回答这种问题！
When I am creating a new database table, what factors should I take into account for selecting the primary key's data type?
Sorry to do that, but I found that the answers I gave to related questions (you can check this and this) could apply to this one. I reshaped them a little bit...
You will find many posts dealing with this issue, and each choice you'll make has its pros and cons. Arguments for these usually refer to relational database theory and database performance.
On this subject, my point is very simple: surrogate primary keys ALWAYS work, while Natural keys MIGHT NOT ALWAYS work one of these days, and this for multiple reasons: field too short, rules change, etc.
To this point, you've guessed here that I am basically a member of the uniqueIdentifier/surrogate primary key team, and even if I appreciate and understand arguments such as the ones presented here, I am still looking for the case where "natural" key is better than surrogate ...
In addition to this, one of the most important but always forgotten arguments in favor of this basic rule is related to code normalization and productivity:
each time I create a table, shall I lose time
- identifying its primary key and its physical characteristics (type, size)
- remembering these characteristics each time I want to refer to it in my code?
- explaining my PK choice to other developers in the team?
My answer is no to all of these questions:
- I have no time to lose trying to identify "the best Natural Primary Key" when the surrogate option gives me a bullet-proof solution.
- I do not want to remember that the Primary Key of my Table_whatever is a 10 characters long string when I write the code.
- I don't want to lose my time negotiating the Natural Key length: "well if You need 10 why don't you take 12 to be on the safe side?". This "on the safe side" argument really annoys me: If you want to stay on the safe side, it means that you are really not far from the unsafe side! Choose surrogate: it's bullet-proof!
So I've been working for the last five years with a very basic rule: each table (let's call it 'myTable') has its first field called 'id_MyTable' which is of uniqueIdentifier type. Even if this table supports a "many-to-many" relation, where a field combination offers a very acceptable Primary Key, I prefer to create this 'id_myManyToManyTable' field being a uniqueIdentifier, just to stick to the rule, and because, finally, it does not hurt.
The major advantage is that you don't have to care anymore about the use of Primary Key and/or Foreign Key within your code. Once you have the table name, you know the PK name and type. Once you know which links are implemented in your data model, you'll know the name of available foreign keys in the table.
And if you still want to have your "Natural Key" somewhere in your table, I advise you to build it following a standard model such as
Tbl_whatever
id_whatever, unique identifier, primary key
code_whatever, whateverTypeYouWant(whateverLengthYouEstimateTheRightOne), indexed
.....
Where id_ is the prefix for primary key, and code_ is used for "natural" indexed field. Some would argue that the code_ field should be set as unique. This is true, and it can be easily managed either through DDL or external code. Note that many "natural" keys are calculated (invoice numbers), so they are already generated through code
I am not sure that my rule is the best one. But it is a very efficient one! If everyone was applying it, we would for example avoid time lost answering to this kind of question!
这篇关于选择主键的数据类型时应该考虑什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
