为Products表决定人为主键和自然键 [英] Deciding between an artificial primary key and a natural key for a Products table
问题描述
基本上，我需要将来自多个供应商的产品数据合并到一个单一的数据库（当然比它更复杂），它有几个表，需要连接在一起为大多数OLTP操作。
Basically, I will need to combine product data from multiple vendors into a single database (it's more complex than that, of course) which has several tables that will need to be joined together for most OLTP operations.
我会坚持使用默认值，并使用自动递增的整数作为主键，但是一个供应商提供自己的ProductiD字段，其余的不会，我会有做很多手动映射到其他表然后加载数据（因为我不得不先将其加载到Products表，然后拉出ID，并添加其他信息，我需要到其他表）
I was going to stick with the default and use an auto-incrementing integer as the primary key, but while one vendor supplies their own "ProductiD" field, the rest do not and I would have to do a lot of manual mapping to the other tables then to load the data (as I would have to first load it into the Products table, then pull the ID out and add that along with the other information I need to the other tables).
或者，我可以使用产品的SKU作为主键，因为SKU对于单个产品是唯一的，并且所有供应商在其数据馈送中提供SKU 。如果我使用SKU作为PK，那么我可以很容易地加载数据源，因为一切都基于SKU，这是它在现实世界中的工作原理。然而，SKU是字母数字的，并且可能比基于整数的键稍微低一些。
Alternatively, I could use the product's SKU as it's primary key since the SKU is unique for a single product, and all of the vendors supply a SKU in their data feeds. If I use the SKU as the PK then I could easily load the data feeds as everything is based off of the SKU, which is how it works in the real world. However the SKU is alphanumeric and will probably be slightly less efficient than an integer-based key.
我应该看看什么想法？
推荐答案
这是代理和自然主键。
IMHO始终赞成代理主键。主键不应该有意义，因为意义可以改变。甚至国名也可以改变，国家可以存在和消失，更不用说产品。
IMHO always favour surrogate primary keys. Primary keys shouldn't have meaning because that meaning can change. Even country names can change and countries can come into existence and disappear, let alone products. Changing primary keys is definitely not advised, which can happen with natural keys.
有关代理与主键：
那么代理键赢了吗？好了，
让我们来看看是否有任何
con的自然键适用于
代理键：
So surrogate keys win right? Well, let’s review and see if any of the con’s of natural key’s apply to surrogate keys:
- Con 1：主键大小 - 代理键通常没有问题
具有索引大小，因为它们通常是
a int类型的单列。 - Con 2：外键大小 - 它们没有外键或外键
索引大小问题，对于
与Con 1相同的原因。 - Con 3：Asthetics - 好吧，这是beholder类型的东西，但
他们肯定不涉及写
与复合自然
键一样多的代码。 - Con 4& 5：可选项适用性 - 代理键没有
的问题，人或事不是
想要或不能
提供数据。 - Con 6：唯一性 - 他们100％保证是独一无二的。
- Con 7：隐私权 - 如果
不道德的人获得他们，他们就没有隐私问题。 - Con 8：意外反规范化 - 您不能意外地将
非商业数据反规范化。 - Con 9：级联更新 - 代理键不更改，因此没有
担心如何在
更新上级联它们。 - Con 10：Varchar联接速度 - 他们通常是int，所以他们通常是
- Con 1: Primary key size – Surrogate keys generally don't have problems with index size since they're usually a single column of type int. That's about as small as it gets.
- Con 2: Foreign key size - They don't have foreign key or foreign index size problems either for the same reason as Con 1.
- Con 3: Asthetics - Well, it’s an eye of the beholder type thing, but they certainly don’t involve writing as much code as with compound natural keys.
- Con 4 & 5: Optionality & Applicability – Surrogate keys have no problems with people or things not wanting to or not being able to provide the data.
- Con 6: Uniqueness - They are 100% guaranteed to be unique. That’s a relief.
- Con 7: Privacy - They have no privacy concerns should an unscrupulous person obtain them.
- Con 8: Accidental Denormalization – You can’t accidentally denormalize non-business data.
- Con 9: Cascading Updates - Surrogate keys don't change, so no worries about how to cascade them on update.
- Con 10: Varchar join speed - They're generally int's, so they're generally as fast to join over as you can get.
这篇关于为Products表决定人为主键和自然键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
