In our dev group we have a raging debate regarding the naming convention for Primary and Foreign Keys. There's basically two schools of thought in our group:
在我们的开发小组中，关于主键和外键的命名约定有一场激烈的争论。我们组基本上有两种思想流派:
1:
1:
Primary Table (Employee)
Primary Key is called ID
Foreign table (Event)
Foreign key is called EmployeeID
or
或
2:
2:
Primary Table (Employee)
Primary Key is called EmployeeID
Foreign table (Event)
Foreign key is called EmployeeID
I prefer not to duplicate the name of the table in any of the columns (So I prefer option 1 above). Conceptually, it is consistent with a lot of the recommended practices in other languages, where you don't use the name of the object in its property names. I think that naming the foreign key EmployeeID (or Employee_ID might be better) tells the reader that it is the ID column of the Employee Table.
我不喜欢在任何列中重复表的名称(因此我更喜欢上面的选项1)。从概念上讲，它与其他语言中许多推荐的实践是一致的，在这些实践中，您不会在对象的属性名中使用对象的名称。我认为命名外键EmployeeID(或Employee_ID可能更好)告诉读者它是Employee表的ID列。
Some others prefer option 2 where you name the primary key prefixed with the table name so that the column name is the same throughout the database. I see that point, but you now can not visually distinguish a primary key from a foreign key.
有些人更喜欢选项2，其中您将以表名为前缀的主键命名，以便整个数据库的列名都是相同的。我明白这一点，但是您现在不能从视觉上区分主键和外键。
Also, I think it's redundant to have the table name in the column name, because if you think of the table as an entity and a column as a property or attribute of that entity, you think of it as the ID attribute of the Employee, not the EmployeeID attribute of an employee. I don't go an ask my coworker what his PersonAge or PersonGender is. I ask him what his Age is.
同时,我认为这是多余的列名称的表名,因为如果你认为表作为一个实体,一个列的属性或属性的实体,你认为这是员工的ID属性,不是EmployeeID属性的一个员工。我不会去问我的同事他的人物或性别是什么。我问他多大了。
So like I said, it's a raging debate and we go on and on and on about it. I'm interested to get some new perspectives.
就像我说的，这是一场激烈的辩论，我们一直在讨论这个问题。我有兴趣获得一些新的观点。
13 个解决方案
#1
43
It doesn't really matter. I've never run into a system where there is a real difference between choice 1 and choice 2.
它并不重要。我从来没有遇到过一个系统在选择1和选择2之间存在真正的区别。
Jeff Atwood had a great article a while back on this topic. Basically people debate and argue the most furiously those topics which they cannot be proven wrong on. Or from a different angle, those topics which can only be won through filibuster style endurance based last-man-standing arguments.
杰夫·阿特伍德有一篇关于这个话题的好文章。基本上人们最激烈地辩论和争论那些他们不能被证明是错误的话题。或者从另一个角度来说，那些只能通过冗长演说来赢得的话题。
Pick one and tell them to focus on issues that actually impact your code.
选择一个，告诉他们关注真正影响代码的问题。
EDIT: If you want to have fun, have them specify at length why their method is superior for recursive table references.
编辑:如果你想玩得开心，让他们详细说明为什么递归表引用的方法更好。
#2
65
If the two columns have the same name in both tables (convention #2), you can use the USING syntax in SQL to save some typing and some boilerplate noise:
如果两个列在两个表中都有相同的名称(约定#2)，您可以使用SQL中的USING语法来保存一些输入和一些样板文件噪音:
SELECT name, address, amount
FROM employees JOIN payroll USING (employee_id)
Another argument in favor of convention #2 is that it's the way the relational model was designed.
另一个支持惯例#2的观点是它是关系模型设计的方式。
The significance of each column is partially conveyed by labeling it with the name of the corresponding domain.
每一列的意义部分是通过用相应域的名称来表示的。
#3
11
I think it depends on your how you application is put together. If you use ORM or design your tables to represent objects then option 1 may be for you.
我认为这取决于你的应用是如何组合在一起的。如果您使用ORM或设计您的表来表示对象，那么选项1可能适合您。
I like to code the database as its own layer. I control everything and the app just calls stored procedures. It is nice to have result sets with complete column names, especially when there are many tables joined and many columns returned. With this stype of application, I like option 2. I really like to see column names match on joins. I've worked on old systems where they didn't match and it was a nightmare,
我喜欢将数据库编码为它自己的层。我控制一切，应用程序只调用存储过程。拥有具有完整列名的结果集是很好的，特别是当有许多表被连接并且返回许多列时。有了这个应用程序，我喜欢选项2。我真的喜欢看到列名在连接上匹配。我在旧的系统中工作过他们不匹配，这是一场噩梦，
#4
3
Neither convention works in all cases, so why have one at all? Use Common sense...
这两种惯例在所有情况下都不起作用，那么为什么要有一种呢?用常识……
e.g., for self-referencing table, when there are more than one FK column that self-references the same table's PK, you HAVE to violate both "standards", since the two FK columns can't be named the same... e.g., EmployeeTable with EmployeeId PK, SupervisorId FK, MentorId Fk, PartnerId FK, ...
例如，对于自引用表，当有多个FK列自引用同一个表的PK时，您必须违反两个“标准”，因为两个FK列不能被命名为相同的…例:员工可与员工PK，监管FK, MentorId FK, PartnerId FK，…
#5
3
I agree that there is little to choose between them. To me a much more significant thing about either standard is the "standard" part.
我同意他们之间没什么可选择的。对我来说，这两种标准中更重要的是“标准”部分。
If people start 'doing their own thing' they should be strung up by their nethers. IMHO :)
如果人们开始“做他们自己的事”，他们应该被他们的朋友绑起来。IMHO:)
#6
2
If you are looking at application code, not just database queries, some things seem clear to me:
如果您正在查看应用程序代码，而不仅仅是数据库查询，那么在我看来，有些事情似乎是清楚的:
-
Table definitions usually directly map to a class that describes one object, so they should be singular. To describe a collection of an object, I usually append "Array" or "List" or "Collection" to the singular name, as it more clearly than use of plurals indicates not only that it is a collection, but what kind of a collection it is. In that view, I see a table name as not the name of the collection, but the name of the type of object of which it is a collection. A DBA who doesn't write application code might miss this point.
表定义通常直接映射到描述一个对象的类，因此它们应该是单数的。为了描述一个对象的集合，我通常将“数组”或“列表”或“集合”附加到单个名称，因为它比使用复数更清楚地表明它是一个集合，而且还表明它是什么类型的集合。在该视图中，我看到表名不是集合的名称，而是作为集合的对象类型的名称。不编写应用程序代码的DBA可能会漏掉这一点。
-
The data I deal with often uses "ID" for non-key identification purposes. To eliminate confusion between key "ID"s and non-key "ID"s, for the primary key name, we use "Key" (that's what it is, isn't it?) prefixed with the table name or an abbreviation of the table name. This prefixing (and I reserve this only for the primary key) makes the key name unique, which is especially important because we use variable names that are the same as the database column names, and most classes have a parent, identified by the name of the parent key. This also is needed to make sure that it is not a reserved keyword, which "Key" alone is. To facilitate keeping key variable names consistent, and to provide for programs that do natural joins, foreign keys have the same name as is used in the table in which they are the primary key. I have more than once encountered programs which work much better this way using natural joins. On this last point, I admit a problem with self-referencing tables, which I have used. In this case, I would make an exception to the foreign key naming rule. For example, I would use ManagerKey as a foreign key in the Employee table to point to another record in that table.
我处理的数据经常使用“ID”作为非密钥标识。为了消除键“ID”和非键“ID”之间的混淆，对于主键名，我们使用带有表名或表名缩写的“key”(这就是它，不是吗?)这个前缀(我只保留主键)使键名惟一，这一点尤为重要，因为我们使用与数据库列名相同的变量名，而且大多数类都有一个父类，由父键名标识。这也需要确保它不是一个保留的关键字，而“Key”是唯一的关键字。为了便于保持键变量名的一致性，并为进行自然连接的程序提供支持，外键具有与作为主键的表中使用的名称相同的名称。我不止一次遇到过使用自然连接以这种方式工作得更好的程序。在最后一点上，我承认我使用的自引用表有一个问题。在这种情况下，我将对外键命名规则做一个例外。例如，我将使用ManagerKey作为Employee表中的外键，以指向该表中的另一个记录。
#7
2
I like convention #2 - in researching this topic, and finding this question before posting my own, I ran into the issue where:
我喜欢惯例2——在研究这个话题，在发布我自己的问题之前找到这个问题时，我遇到了这样的问题:
I am selecting * from a table with a large number of columns and joining it to a second table that similarly has a large number of columns. Both tables have an "id" column as the primary key, and that means I have to specifically pick out every column (as far as I know) in order to make those two values unique in the result, i.e.:
我从一个具有大量列的表中选择*，并将其连接到另一个类似具有大量列的表。这两个表都有一个“id”列作为主键，这意味着我必须特别挑选出每个列(据我所知)，以便使这两个值在结果中是惟一的，即:
SELECT table1.id AS parent_id, table2.id AS child_id
Though using convention #2 means I will still have some columns in the result with the same name, I can now specify which id I need (parent or child) and, as Steven Huwig suggested, the USING statement simplifies things further.
尽管使用约定#2意味着在结果中仍然有一些列具有相同的名称，但我现在可以指定我需要哪个id(父id或子id)，正如Steven Huwig所建议的，using语句进一步简化了事情。
#8
2
I've always used userId as a PK on one table and userId on another table as a FK. 'm seriously thinking about using userIdPK and userIdFK as names to identify one from the other. It will help me to identify PK and FK quickly when looking at the tables and it seems like it will clear up code when using PHP/SQL to access data making it easier to understand. Especially when someone else looks at my code.
我总是在一个表上使用userId作为PK，在另一个表上使用userId作为FK。我在认真地考虑使用userIdPK和userIdFK作为名称，从另一个中识别一个。它将帮助我在查看表时快速识别PK和FK，并且看起来它将在使用PHP/SQL访问数据时清除代码，使其更容易理解。特别是当别人看到我的代码时。
#9
1
The convention we use where I work is pretty close to A, with the exception that we name tables in the plural form (ie, "employees") and use underscores between the table and column name. The benefit of it is that to refer to a column, it's either "employees _ id" or "employees.id", depending on how you want to access it. If you need to specify what table the column is coming from, "employees.employees _ id" is definitely redundant.
在我工作的地方，我们使用的约定与A非常接近，但我们使用复数形式的表(即“employees”)，并在表和列名之间使用下划线。它的好处是可以引用一个列，它要么是“雇员_ id”，要么是“雇员”。id，这取决于你想怎么访问它。如果您需要指定列来自哪个表，“employees”。员工id绝对是多余的。
#10
1
I use convention #2. I'm working with a legacy data model now where I don't know what stands for in a given table. Where's the harm in being verbose?
我使用公约# 2。我现在使用的是遗留数据模型，我不知道在给定的表中代表什么。啰嗦有什么害处呢?
#11
1
How about naming the foreign key
如何命名外键
role_id
role_id
where role is the role the referenced entity has relativ to the table at hand. This solves the issue of recursive reference and multiple fks to the same table.
其中角色是被引用的实体与手边的表有关系的角色。这就解决了同一个表的递归引用和多个fks的问题。
In many cases will be identical to the referenced table name. In this cases it becomes identically to one of your proposals.
在许多情况下，将与引用的表名相同。在这种情况下，它与你的一个建议是一致的。
In any case havin long arguments is a bad idea
在任何情况下，长期争论都是一个坏主意。
#12
1
Have you considered the following?
你考虑过以下问题吗?
Primary Table (Employee)
Primary Key is PK_Employee
Foreign table (Event)
Foreign key is called FK_Employee
#13
0
"Where in "employee INNER JOIN order ON order.employee_id = employee.id" is there a need for additional qualification?".
“员工内部连接顺序”。employee_id =员工。id“是否需要额外的资格?”
There is no need for additional qualification because the qualification I talked of is already there.
不需要额外的资格，因为我所说的资格已经存在了。
"the reason that a business user refers to Order ID or Employee ID is to provide context, but at a dabase level you already have context because you are refereing to the table".
“业务用户引用订单ID或员工ID的原因是提供上下文，但在dabase级别，您已经有了上下文，因为您正在引用表”。
Pray, tell me, if the column is named 'ID', then how is that "refereing [sic] to the table" done exactly, unless by qualifying this reference to the ID column exactly in the way I talked of ?
求求你，告诉我，如果列被命名为“ID”，那么“引用[sic]到表”是怎么做到的?
#1
43
It doesn't really matter. I've never run into a system where there is a real difference between choice 1 and choice 2.
它并不重要。我从来没有遇到过一个系统在选择1和选择2之间存在真正的区别。
Jeff Atwood had a great article a while back on this topic. Basically people debate and argue the most furiously those topics which they cannot be proven wrong on. Or from a different angle, those topics which can only be won through filibuster style endurance based last-man-standing arguments.
杰夫·阿特伍德有一篇关于这个话题的好文章。基本上人们最激烈地辩论和争论那些他们不能被证明是错误的话题。或者从另一个角度来说，那些只能通过冗长演说来赢得的话题。
Pick one and tell them to focus on issues that actually impact your code.
选择一个，告诉他们关注真正影响代码的问题。
EDIT: If you want to have fun, have them specify at length why their method is superior for recursive table references.
编辑:如果你想玩得开心，让他们详细说明为什么递归表引用的方法更好。
#2
65
If the two columns have the same name in both tables (convention #2), you can use the USING syntax in SQL to save some typing and some boilerplate noise:
如果两个列在两个表中都有相同的名称(约定#2)，您可以使用SQL中的USING语法来保存一些输入和一些样板文件噪音:
SELECT name, address, amount
FROM employees JOIN payroll USING (employee_id)
Another argument in favor of convention #2 is that it's the way the relational model was designed.
另一个支持惯例#2的观点是它是关系模型设计的方式。
The significance of each column is partially conveyed by labeling it with the name of the corresponding domain.
每一列的意义部分是通过用相应域的名称来表示的。
#3
11
I think it depends on your how you application is put together. If you use ORM or design your tables to represent objects then option 1 may be for you.
我认为这取决于你的应用是如何组合在一起的。如果您使用ORM或设计您的表来表示对象，那么选项1可能适合您。
I like to code the database as its own layer. I control everything and the app just calls stored procedures. It is nice to have result sets with complete column names, especially when there are many tables joined and many columns returned. With this stype of application, I like option 2. I really like to see column names match on joins. I've worked on old systems where they didn't match and it was a nightmare,
我喜欢将数据库编码为它自己的层。我控制一切，应用程序只调用存储过程。拥有具有完整列名的结果集是很好的，特别是当有许多表被连接并且返回许多列时。有了这个应用程序，我喜欢选项2。我真的喜欢看到列名在连接上匹配。我在旧的系统中工作过他们不匹配，这是一场噩梦，
#4
3
Neither convention works in all cases, so why have one at all? Use Common sense...
这两种惯例在所有情况下都不起作用，那么为什么要有一种呢?用常识……
e.g., for self-referencing table, when there are more than one FK column that self-references the same table's PK, you HAVE to violate both "standards", since the two FK columns can't be named the same... e.g., EmployeeTable with EmployeeId PK, SupervisorId FK, MentorId Fk, PartnerId FK, ...
例如，对于自引用表，当有多个FK列自引用同一个表的PK时，您必须违反两个“标准”，因为两个FK列不能被命名为相同的…例:员工可与员工PK，监管FK, MentorId FK, PartnerId FK，…
#5
3
I agree that there is little to choose between them. To me a much more significant thing about either standard is the "standard" part.
我同意他们之间没什么可选择的。对我来说，这两种标准中更重要的是“标准”部分。
If people start 'doing their own thing' they should be strung up by their nethers. IMHO :)
如果人们开始“做他们自己的事”，他们应该被他们的朋友绑起来。IMHO:)
#6
2
If you are looking at application code, not just database queries, some things seem clear to me:
如果您正在查看应用程序代码，而不仅仅是数据库查询，那么在我看来，有些事情似乎是清楚的:
-
Table definitions usually directly map to a class that describes one object, so they should be singular. To describe a collection of an object, I usually append "Array" or "List" or "Collection" to the singular name, as it more clearly than use of plurals indicates not only that it is a collection, but what kind of a collection it is. In that view, I see a table name as not the name of the collection, but the name of the type of object of which it is a collection. A DBA who doesn't write application code might miss this point.
表定义通常直接映射到描述一个对象的类，因此它们应该是单数的。为了描述一个对象的集合，我通常将“数组”或“列表”或“集合”附加到单个名称，因为它比使用复数更清楚地表明它是一个集合，而且还表明它是什么类型的集合。在该视图中，我看到表名不是集合的名称，而是作为集合的对象类型的名称。不编写应用程序代码的DBA可能会漏掉这一点。
-
The data I deal with often uses "ID" for non-key identification purposes. To eliminate confusion between key "ID"s and non-key "ID"s, for the primary key name, we use "Key" (that's what it is, isn't it?) prefixed with the table name or an abbreviation of the table name. This prefixing (and I reserve this only for the primary key) makes the key name unique, which is especially important because we use variable names that are the same as the database column names, and most classes have a parent, identified by the name of the parent key. This also is needed to make sure that it is not a reserved keyword, which "Key" alone is. To facilitate keeping key variable names consistent, and to provide for programs that do natural joins, foreign keys have the same name as is used in the table in which they are the primary key. I have more than once encountered programs which work much better this way using natural joins. On this last point, I admit a problem with self-referencing tables, which I have used. In this case, I would make an exception to the foreign key naming rule. For example, I would use ManagerKey as a foreign key in the Employee table to point to another record in that table.
我处理的数据经常使用“ID”作为非密钥标识。为了消除键“ID”和非键“ID”之间的混淆，对于主键名，我们使用带有表名或表名缩写的“key”(这就是它，不是吗?)这个前缀(我只保留主键)使键名惟一，这一点尤为重要，因为我们使用与数据库列名相同的变量名，而且大多数类都有一个父类，由父键名标识。这也需要确保它不是一个保留的关键字，而“Key”是唯一的关键字。为了便于保持键变量名的一致性，并为进行自然连接的程序提供支持，外键具有与作为主键的表中使用的名称相同的名称。我不止一次遇到过使用自然连接以这种方式工作得更好的程序。在最后一点上，我承认我使用的自引用表有一个问题。在这种情况下，我将对外键命名规则做一个例外。例如，我将使用ManagerKey作为Employee表中的外键，以指向该表中的另一个记录。
#7
2
I like convention #2 - in researching this topic, and finding this question before posting my own, I ran into the issue where:
我喜欢惯例2——在研究这个话题，在发布我自己的问题之前找到这个问题时，我遇到了这样的问题:
I am selecting * from a table with a large number of columns and joining it to a second table that similarly has a large number of columns. Both tables have an "id" column as the primary key, and that means I have to specifically pick out every column (as far as I know) in order to make those two values unique in the result, i.e.:
我从一个具有大量列的表中选择*，并将其连接到另一个类似具有大量列的表。这两个表都有一个“id”列作为主键，这意味着我必须特别挑选出每个列(据我所知)，以便使这两个值在结果中是惟一的，即:
SELECT table1.id AS parent_id, table2.id AS child_id
Though using convention #2 means I will still have some columns in the result with the same name, I can now specify which id I need (parent or child) and, as Steven Huwig suggested, the USING statement simplifies things further.
尽管使用约定#2意味着在结果中仍然有一些列具有相同的名称，但我现在可以指定我需要哪个id(父id或子id)，正如Steven Huwig所建议的，using语句进一步简化了事情。
#8
2
I've always used userId as a PK on one table and userId on another table as a FK. 'm seriously thinking about using userIdPK and userIdFK as names to identify one from the other. It will help me to identify PK and FK quickly when looking at the tables and it seems like it will clear up code when using PHP/SQL to access data making it easier to understand. Especially when someone else looks at my code.
我总是在一个表上使用userId作为PK，在另一个表上使用userId作为FK。我在认真地考虑使用userIdPK和userIdFK作为名称，从另一个中识别一个。它将帮助我在查看表时快速识别PK和FK，并且看起来它将在使用PHP/SQL访问数据时清除代码，使其更容易理解。特别是当别人看到我的代码时。
#9
1
The convention we use where I work is pretty close to A, with the exception that we name tables in the plural form (ie, "employees") and use underscores between the table and column name. The benefit of it is that to refer to a column, it's either "employees _ id" or "employees.id", depending on how you want to access it. If you need to specify what table the column is coming from, "employees.employees _ id" is definitely redundant.
在我工作的地方，我们使用的约定与A非常接近，但我们使用复数形式的表(即“employees”)，并在表和列名之间使用下划线。它的好处是可以引用一个列，它要么是“雇员_ id”，要么是“雇员”。id，这取决于你想怎么访问它。如果您需要指定列来自哪个表，“employees”。员工id绝对是多余的。
#10
1
I use convention #2. I'm working with a legacy data model now where I don't know what stands for in a given table. Where's the harm in being verbose?
我使用公约# 2。我现在使用的是遗留数据模型，我不知道在给定的表中代表什么。啰嗦有什么害处呢?
#11
1
How about naming the foreign key
如何命名外键
role_id
role_id
where role is the role the referenced entity has relativ to the table at hand. This solves the issue of recursive reference and multiple fks to the same table.
其中角色是被引用的实体与手边的表有关系的角色。这就解决了同一个表的递归引用和多个fks的问题。
In many cases will be identical to the referenced table name. In this cases it becomes identically to one of your proposals.
在许多情况下，将与引用的表名相同。在这种情况下，它与你的一个建议是一致的。
In any case havin long arguments is a bad idea
在任何情况下，长期争论都是一个坏主意。
#12
1
Have you considered the following?
你考虑过以下问题吗?
Primary Table (Employee)
Primary Key is PK_Employee
Foreign table (Event)
Foreign key is called FK_Employee
#13
0
"Where in "employee INNER JOIN order ON order.employee_id = employee.id" is there a need for additional qualification?".
“员工内部连接顺序”。employee_id =员工。id“是否需要额外的资格?”
There is no need for additional qualification because the qualification I talked of is already there.
不需要额外的资格，因为我所说的资格已经存在了。
"the reason that a business user refers to Order ID or Employee ID is to provide context, but at a dabase level you already have context because you are refereing to the table".
“业务用户引用订单ID或员工ID的原因是提供上下文，但在dabase级别，您已经有了上下文，因为您正在引用表”。
Pray, tell me, if the column is named 'ID', then how is that "refereing [sic] to the table" done exactly, unless by qualifying this reference to the ID column exactly in the way I talked of ?
求求你，告诉我，如果列被命名为“ID”，那么“引用[sic]到表”是怎么做到的?