身份插入与元数据表一起使用是否合适
我的数据库中有几个表，其中只包含“元数据”。 例如，我们有不同的组类型、内容项类型、语言等。
问题是，如果您使用自动编号，则可能会产生间隙。 id 在我们的代码中使用，因此编号非常重要。
现在我想知道在这些表中不要使用自动编号是否更好？
现在我们首先在数据库中创建行，然后才能编写代码。在我看来，情况不应该如此。
你们觉得怎么样？
I have several tables within my database that contains nothing but "metadata".
For example we have different grouptypes, contentItemTypes, languages, ect.
the problem is, if you use automatic numbering then it is possible that you create gaps.
The id's are used within our code so, the number is very important.
Now I wonder if it isn't better not to use autonumbering within these tables?
Now we have create the row in the database first, before we can write our code. And in my opinion this should not be the case.
What do you guys think?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
我将使用您建议的标识列作为主键（代理键），然后将您的候选键（系统中的标识符）分配为标准列，但对其应用唯一约束。这样您可以确保不插入重复的记录。
有道理吗？
I would use an identity column as you suggest to be your primary key(surrogate key) and then assign your you candidate key (identifier from your system) to be a standard column but apply a unique constraint to it. This way you can ensure you do not insert duplicate records.
Make sense?
如果这些是 FK 表，仅用于将代码扩展为描述或包含其他属性，那么我不会使用 IDENTITY。身份有利于插入用户数据，元数据表通常是静态的。当您部署代码更新时，您不希望感到惊讶并且 IDENTITY 值与您预期不同。
例如，您向“语言”表添加一个新值，您期望 ID 为 6，但由于某种原因（开发不同步、另一个人尚未实现他们的下一个语言类型等）您的下一个身份get 与 7 不同。然后，您使用语言 ID=6 插入或转换一堆行，这些行都失败，因为它不存在（元数据表中为 7）。更糟糕的是，它们实际上都插入或更新，因为您认为属于您的值 6 已经存在于元数据表中，并且您现在混合了两个共享相同值 6 的项目，而您的新值 7 未被使用。
我会根据您需要的代码数量、您需要查看它的频率来选择正确的数据类型（CHAR 很适合查看一些值，有助于记忆）。
例如，如果您只有几个组，并且经常查看原始数据，那么 char(1) 可能会很好：
但是，如果有许多不同的值，那么某种形式的 int (tinyint, Smallint、int、bigint) 可以这样做：
if these are FK tables used just to expand codes into a description or contain other attributes, then I would NOT use an IDENTITY. Identity are good for ever inserting user data, metadata tables are usually static. When you deploy a update to your code, you don't want to be suprised and have an IDENTITY value different than you expect.
For example, you add a new value to the "Languages" table, you expect the ID will be 6, but for some reason (development is out of sync, another person has not implemented their next language type, etc) the next identity you get is different say 7. You then insert or convert a bunch of rows having using Language ID=6 which all fail becuase it does not exist (it is 7 iin the metadata table). Worse yet, they all actuall insert or update because the value 6 you thought was yours was already in the medadata table and you now have a mix of two items sharing the same 6 value, and your new 7 value is left unused.
I would pick the proper data type based on how many codes you need, how often you will need to look at it (CHARs are nice to look at for a few values, helps with memory).
for example, if you only have a few groups, and you'll often look at the raw data, then a char(1) may be good:
however, if there are many different values, then some form of an int (tinyint, smallint, int, bigint) may do it:
如果数字硬编码在代码中，请勿使用身份字段。在数据库中对它们进行硬编码，这样它们就不太容易因为有人糟糕地编写数据库脚本而发生更改。
If the numbers are hardcoded in your code, don't use identity fields. Hardcode them in the database as well as they'll be less prone to changing because someone scripted a database badly.
我会使用标识列作为主键，也只是为了简单地将记录插入数据库，然后使用元数据类型的列，我称之为 LookUpType(int)，以及 LookUpId 的列（int value在代码中）或选择列表中的值 LookUpName(string)，如果这些值需要其他设置，可以说使用额外的列。我个人使用两个附加项，LookUpKey 用于层次关系，LookUpValue 用于 LookUpName 的缩写或替代值。
I would use an identity column as the primary key also just for simplicity sake of inserting the records into the database, but then use a column for type of metadata, I call mine LookUpType(int), as well as columns for LookUpId (int value in code) or value in select lists, LookUpName(string), and if those values require additional settings so to speak use extra columns. I personally use two extras, LookUpKey for hierarchical relations, and LookUpValue for abbreviations or alternate values of LookUpName.
好吧，如果这些数字对您很重要，因为它们会出现在代码中，那么我可能不会使用 IDENTITY。
相反，只需确保使用 INT 列并将其设为主键 - 在这种情况下，您必须自己提供 ID，并且它们必须是唯一的。
Well, if those numbers are important to you because they'll be in code, I would probably not use an IDENTITY.
Instead, just make sure you use a INT column and make it the primary key - in that case, you will have to provide the ID's yourself, and they'll have to be unique.