代理键/序列号/ID 列的备用名称
我有一个遗留表，它的自然键的一部分是一个名为 ID，所以我倾向于将其命名为 SURROGATE_KEY。我的所有其他表都使用
I have a legacy table that has as a part of its natural key a column named <table_name>_IDENTIFIER and it seems like it would be confusing to create a surrogate key named <table_name>_ID or ID so I'm leaning towards naming it SURROGATE_KEY. All my other tables use the <table_name>_ID syntax. Any better suggestions?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
不要将其称为 SURROGATE_KEY。这在任何其他情况下都是毫无意义的。我会坚持使用
_ID Don't call it SURROGATE_KEY. That is meaningless in any other context. I'd stick with
<table_name>_ID. Yes it's a little confusing. But, given your established convention, anything else would be confusing too.我可能建议您采用您的标准：_ID
最终，旧表将不再是驱动力，并且 IDENTIFIER 列将看起来很奇怪，这就是您想要的想要，而不是 - '哦，是的，我需要使用 surrogate_key 来代替 id...' 时刻。
I might suggest that you go with your standard:
<table_name>_IDEventually, the legacy table will not be the driving force, and it will be the IDENTIFIER column that will look odd, which is what you want, as opposed to that - 'oh yeah, i need to use surrogate_key for that thing instead of id...' moment.
首先，我不会在列中包含表名称。列是一个属性，需要其所属实体的上下文。例如，如果没有它所属的上下文，那么拥有一个“名称”是没有用的。您需要知道它是一个人的名字或公司名称等，并且您在实体本身的名称中拥有它。因此，我不会在列前面加上声明它的表的名称。
这给你留下了像“Id”、“Key”、“SurrogateKey”或者“SystemId”这样的选择，这些选择都同样模糊。至少“SurrogateKey”描述了它是什么，这是一个额外的好处。该名称对于 DBA 来说有意义，但对于开发人员来说可能没有意义（尽管他们应该理解这个概念）。在这些选择中，我倾向于使用“Id”并找到一种方法将
_Identifier First, I would not include the table name in my columns. A column is an attribute which requires the context of the entity to which it belongs. Having a "name" for example without the context to which it belongs is of no use. You need to know it is a Person's name or a Company name etc. and you have that in the name of the entity itself. Thus, I would not prefix columns with the name of the table in which it is declared.
That leaves you with choices like "Id", "Key", "SurrogateKey", or perhaps "SystemId" which are all equally vague. At least "SurrogateKey" describes what it is which is a bonus. That name will make sense to a DBA but perhaps not a developer (although they should understand the concept). Of those choices, I'd be inclined to use "Id" and find a way to change
<table_name>_Identifierto something more descriptive.在数据建模世界中，在绘制 ER 模型期间，像 SURROGATE_KEY（或 SURROGATE_ID）这样的代理键在创建外键约束时肯定会引起痛苦的副作用。
即，在大多数 DM 工具中通过拖放主键将父项与子项链接起来将自动在子项中创建相同的列，并在列名中生成重复项。
根据经验，为了避免这种情况，将代理键命名为 Table_name.Table_name_ID 或 Table_name._ID 可能是一个不错的选择。
In Data Modelling world during drawing ER model, Surrogate key like SURROGATE_KEY (or SURROGATE_ID) will definitely cause pain side-effects when creating Foreign Key Constraint.
I.e. linking parent with child in majority of DM tools via dragg-n-dropping primary key will automatically create identical column in a child generating dups in column names.
To avoid that as a rule of thumb, naming Surrogate key like Table_name.Table_name_ID or Table_name._ID can be good option.
同意。 。 。不建议使用 SURROGATE_ID。所有这些建议似乎都缺乏数据管理和数据的核心。数据建模最佳实践：建立（并一致使用！）命名约定和命名规则值域。建议：
1. 如果数据库或编程协议（如 .NET，据我了解，它厌恶自然主键）需要将单个无意义的整数指定为主键（代理项），则创建一个值“Id”和“Id”的域将其定义为带有代理主键描述的整数数据类型。
2. 命名属性/列时，使用域“Id”的唯一列将是用分配的整数值填充的代理（主）键列。不允许其他属性/列使用域“Id”，因此从属性/列名称可以绝对清楚存储的值的性质以及如何开始使用这些值。
感谢您的机会！
Agreed . . . SURROGATE_ID is not recommended. What all the suggestions seem to be lacking is at the very heart of data management & data modelling best practices: establishing (& consistently using!) naming conventions & value domains. Suggestions:
1. If the database or programming protocol (like .NET which abhors natural primary keys as I've been lead to understand) requires a single, meaningless, integer assigned as a primary key -- a surrogate -- key, then create a value domain of "Id" & define it as data type integer with description of surrogate primary key.
2. When naming attributes/columns, the ONLY columns using the domain "Id" would be surrogate (primary) key columns populated with assigned integer values. No other attributes/columns would be allowed to use the domain "Id", so it would be absolutely clear from the attribute/column name the nature of the values stored AND how those values are begin utilized.
Thanks for the opportunity!