A friend told me that I should include the table name in the field name of the same table, and I'm wondering why? And should it be like this? Example:
一位朋友告诉我，我应该将表名包含在同一个表的字段名中，我想知道为什么？它应该是这样吗？例：
(Table) Users
(Fields) user_id, username, password, last_login_time
I see that the prefix 'user_' is meaningless since I know it's already for a user. But I'd like to hear from you too. note: I'm programming in php, mysql.
我看到前缀'user_'是没有意义的，因为我知道它已经是一个用户。但我也想听听你的意见。注意：我在php，mysql中编程。
9 个解决方案
#1
12
I agree with you. The only place I am tempted to put the table name or a shortened form of it is on primary and foreign keys or if the "natural" name is a keyword.
我赞同你。我唯一想把表名或缩写形式放在主键和外键上或者“自然”名称是关键字的地方。
Users: id or user_id, username, password, last_login_time
Post: id or post_id, user_id, post_date, content
I generally use 'id' as the primary key field name but in this case I think user_id and post_id are perfectly OK too. Note that the post date was called 'post_date" because 'date' is a keyword.
我通常使用'id'作为主键字段名称，但在这种情况下，我认为user_id和post_id也完全正常。请注意，发布日期称为“post_date”，因为“date”是一个关键字。
At least that's my convention. Your mileage may vary.
至少这是我的惯例。你的旅费可能会改变。
#2
10
I see no reason to include the table name, it's superfluous. In the queries you can refer to the fields as <table name>.<field name> anyway (eg. "user.id").
我认为没有理由包括表名，这是多余的。在查询中，您可以将字段称为
#3
4
With generic fields like 'id' and 'name', it's good to put the table name in.
使用'id'和'name'等通用字段，最好将表名放入。
The reason is it can be confusing when writing joins across multiple tables.
原因是在跨多个表编写连接时可能会造成混淆。
It's personal preference, really, but that is the reasoning behind it (and I always do it this way).
这是个人偏好，真的，但这背后的原因（我总是这样做）。
Whatever method you choose, make sure it is consistent within the project.
无论选择哪种方法，都要确保它在项目中保持一致。
#4
3
Personally I don't add table names for field names in the main table but when using it as a foreign field in another table, I will prefix it with the name of the source table. e.g. The id field on the users table will be called id, but on the comments table it, where comments are linked to the user who posted them, it will be user_id.
我个人不会在主表中添加字段名称的表名，但在将其用作另一个表中的外部字段时，我将在其前面加上源表的名称。例如users表上的id字段将被称为id，但在comments表中，注释链接到发布它们的用户，它将是user_id。
This I picked up from CakePHP's naming scheme and I think it's pretty neat.
这是我从CakePHP的命名方案中获得的，我认为它非常整洁。
#5
2
Prefixing the column name with the table name is a way of guaranteeing unique column names, which makes joining easier.
使用表名前缀列名是一种保证唯一列名的方法，这使得连接更容易。
But it is a tiresome practice, especially if when we have long table names. It's generally easier to just use aliases when appropriate. Besides, it doesn't help when we are self-joining.
但这是一个令人厌倦的做法，特别是如果我们有长桌名称。在适当的时候使用别名通常更容易。此外，当我们自我加入时，它没有帮助。
As a data modeller I do find it hard to be consistent all the time. With ID columns I theoretically prefer to have just ID but I usually find I have tables with columns called USER_ID, ORDER_ID, etc.
作为数据建模者，我发现很难始终保持一致。使用ID列我理论上更喜欢只有ID但我通常会发现我的表有名为USER_ID，ORDER_ID等的列。
There are scenarios where it can be positively beneficial to use a common column name across multiple tables. For instance, when a logical super-type/sub-type relationship has been rendered as just the child tables it is useful to retain the super-type's column on all the sub-type tables (e.g. ITEM_STATUS) instead of renaming it for each sub-type (ORDER_ITEM_STATUS, INVOICE_ITEM_STATUS, etc). This is particularly true when they are enums with a common set of values.
在某些情况下，在多个表中使用公共列名称会非常有益。例如，当逻辑超类型/子类型关系被渲染为子表时，在所有子类型表（例如ITEM_STATUS）上保留超类型列而不是为每个子表重命名它是有用的。 -type（ORDER_ITEM_STATUS，INVOICE_ITEM_STATUS等）。当它们是具有一组共同值的枚举时尤其如此。
#6
1
For example, your database has tables which store information about Sales and Human resource departments, you could name all your tables related to Sales department as shown below:
例如，您的数据库具有存储有关销售和人力资源部门的信息的表，您可以命名与销售部门相关的所有表，如下所示：
SL_NewLeads SL_Territories SL_TerritoriesManagers
SL_NewLeads SL_Territories SL_TerritoriesManagers
You could name all your tables related to Human resources department as shown below:
您可以命名与人力资源部门相关的所有表格，如下所示：
HR_Candidates HR_PremierInstitutes HR_InterviewSchedules
HR_Candidates HR_PremierInstitutes HR_InterviewSchedules
This kind of naming convention makes sure, all the related tables are grouped together when you list all your tables in alphabetical order. However, if your database deals with only one logical group of tables, you need not use this naming convention.
这种命名约定可以确保，当您按字母顺序列出所有表时，所有相关表都会组合在一起。但是，如果数据库仅处理一个逻辑表组，则无需使用此命名约定。
Note that, sometimes you end up vertically partitioning tables into two or more tables, though these partitions effectively represent the same entity. In this case, append a word that best identifies the partition, to the entity name
请注意，有时您最终会将表垂直分区为两个或更多表，尽管这些分区实际上代表了同一个实体。在这种情况下，请将最能标识分区的单词附加到实体名称
#7
1
Actually, there is a reason for that kind of naming, especially when it comes to fields, you're likely to join on. In MySQL at least, you can use the USING keyword instead of ON, then users u JOIN posts p ON p.user_id = u.id becomes users u JOIN posts p USING(user_id) which is cleaner IMO.
实际上，这种命名是有原因的，特别是涉及领域时，你可能会加入。至少在MySQL中，你可以使用USING关键字而不是ON，然后用户你加入帖子p ON p.user_id = u.id成为用户你加入帖子p USING（user_id）这是更清洁的IMO。
Regarding other types of fields, you may benefit when selecting *, because you wouldn't have to specify the list of the fields you need and stay sure of which field comes from which table. But generally the usage SELECT * is discouraged on performance and mainenance grounds, so I consider prefixing such fields with table name a bad practice, although it may differ from application to application.
关于其他类型的字段，在选择*时可能会受益，因为您不必指定所需字段的列表，并确保哪个字段来自哪个表。但一般来说，在性能和维护性方面不鼓励使用SELECT *，所以我认为在这些字段前加上表名是一种不好的做法，尽管它可能因应用程序而异。
#8
0
Sounds like the conclusion is: If the field name is unique across tables - prefix with table name. If the field name has the potential to be duplicated in other tables, name it unique.
听起来像结论是：如果字段名称在表间是唯一的 - 前缀与表名称。如果字段名称可能在其他表中重复，请将其命名为唯一。
I found field names such as "img, address, phone, year" since different tables may include different images, addresses, phone numbers, and years.
我找到了诸如“img，address，phone，year”之类的字段名称，因为不同的表格可能包含不同的图像，地址，电话号码和年份。
#9
0
We should define primary keys with prefix of tablename.
我们应该用tablename的前缀定义主键。
We should use use_id instead if id and post_id instead of just id.
如果id和post_id而不是id，我们应该使用use_id。
Benefits:-
优点： -
1) Easily Readable
1）易于阅读
2) Easily differentiate in join queries. We can minimize the use of alias in query.
2）轻松区分联接查询。我们可以在查询中最小化别名的使用。
user table : user_id(PK)
用户表：user_id（PK）
post table : post_id(PK) user_id(FK) here user table PK and post table FK are same
post表：post_id（PK）user_id（FK）这里用户表PK和post表FK是一样的
As per documentation,
根据文件，
3) This way we can get benefit of NATURAL JOIN and JOIN with USING
3）这样我们可以通过USING获得NATURAL JOIN和JOIN的好处
Natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard. The goal was to align the syntax and semantics of MySQL with respect to NATURAL JOIN and JOIN ... USING according to SQL:2003. However, these changes in join processing can result in different output columns for some joins. Also, some queries that appeared to work correctly in older versions (prior to 5.0.12) must be rewritten to comply with the standard.
USING的自然连接和连接（包括外连接变体）根据SQL：2003标准进行处理。目标是根据SQL：2003将NATURAL的语法和语义与NATURAL JOIN和JOIN ... USING对齐。但是，联接处理中的这些更改可能会导致某些联接的输出列不同。此外，一些似乎在旧版本（5.0.12之前）中正常工作的查询必须重写以符合标准。
These changes have five main aspects:
这些变化有五个主要方面：
1) The way that MySQL determines the result columns of NATURAL or USING join operations (and thus the result of the entire FROM clause).
1）MySQL确定NATURAL或USING连接操作的结果列的方式（以及整个FROM子句的结果）。
2) Expansion of SELECT * and SELECT tbl_name.* into a list of selected columns.
2）将SELECT *和SELECT tbl_name。*扩展为所选列的列表。
3) Resolution of column names in NATURAL or USING joins.
3）在NATURAL或USING连接中解析列名。
4) Transformation of NATURAL or USING joins into JOIN ... ON.
4）将NATURAL或USING连接转换为JOIN ... ON。
5) Resolution of column names in the ON condition of a JOIN ... ON.
5）在JOIN ... ON的ON条件下解析列名。
Examples:-
例子：-
SELECT * FROM user NATURAL LEFT JOIN post;
SELECT * FROM user NATURAL JOIN post;
SELECT * FROM user JOIN post USING (user_id);
#1
12
I agree with you. The only place I am tempted to put the table name or a shortened form of it is on primary and foreign keys or if the "natural" name is a keyword.
我赞同你。我唯一想把表名或缩写形式放在主键和外键上或者“自然”名称是关键字的地方。
Users: id or user_id, username, password, last_login_time
Post: id or post_id, user_id, post_date, content
I generally use 'id' as the primary key field name but in this case I think user_id and post_id are perfectly OK too. Note that the post date was called 'post_date" because 'date' is a keyword.
我通常使用'id'作为主键字段名称，但在这种情况下，我认为user_id和post_id也完全正常。请注意，发布日期称为“post_date”，因为“date”是一个关键字。
At least that's my convention. Your mileage may vary.
至少这是我的惯例。你的旅费可能会改变。
#2
10
I see no reason to include the table name, it's superfluous. In the queries you can refer to the fields as <table name>.<field name> anyway (eg. "user.id").
我认为没有理由包括表名，这是多余的。在查询中，您可以将字段称为
#3
4
With generic fields like 'id' and 'name', it's good to put the table name in.
使用'id'和'name'等通用字段，最好将表名放入。
The reason is it can be confusing when writing joins across multiple tables.
原因是在跨多个表编写连接时可能会造成混淆。
It's personal preference, really, but that is the reasoning behind it (and I always do it this way).
这是个人偏好，真的，但这背后的原因（我总是这样做）。
Whatever method you choose, make sure it is consistent within the project.
无论选择哪种方法，都要确保它在项目中保持一致。
#4
3
Personally I don't add table names for field names in the main table but when using it as a foreign field in another table, I will prefix it with the name of the source table. e.g. The id field on the users table will be called id, but on the comments table it, where comments are linked to the user who posted them, it will be user_id.
我个人不会在主表中添加字段名称的表名，但在将其用作另一个表中的外部字段时，我将在其前面加上源表的名称。例如users表上的id字段将被称为id，但在comments表中，注释链接到发布它们的用户，它将是user_id。
This I picked up from CakePHP's naming scheme and I think it's pretty neat.
这是我从CakePHP的命名方案中获得的，我认为它非常整洁。
#5
2
Prefixing the column name with the table name is a way of guaranteeing unique column names, which makes joining easier.
使用表名前缀列名是一种保证唯一列名的方法，这使得连接更容易。
But it is a tiresome practice, especially if when we have long table names. It's generally easier to just use aliases when appropriate. Besides, it doesn't help when we are self-joining.
但这是一个令人厌倦的做法，特别是如果我们有长桌名称。在适当的时候使用别名通常更容易。此外，当我们自我加入时，它没有帮助。
As a data modeller I do find it hard to be consistent all the time. With ID columns I theoretically prefer to have just ID but I usually find I have tables with columns called USER_ID, ORDER_ID, etc.
作为数据建模者，我发现很难始终保持一致。使用ID列我理论上更喜欢只有ID但我通常会发现我的表有名为USER_ID，ORDER_ID等的列。
There are scenarios where it can be positively beneficial to use a common column name across multiple tables. For instance, when a logical super-type/sub-type relationship has been rendered as just the child tables it is useful to retain the super-type's column on all the sub-type tables (e.g. ITEM_STATUS) instead of renaming it for each sub-type (ORDER_ITEM_STATUS, INVOICE_ITEM_STATUS, etc). This is particularly true when they are enums with a common set of values.
在某些情况下，在多个表中使用公共列名称会非常有益。例如，当逻辑超类型/子类型关系被渲染为子表时，在所有子类型表（例如ITEM_STATUS）上保留超类型列而不是为每个子表重命名它是有用的。 -type（ORDER_ITEM_STATUS，INVOICE_ITEM_STATUS等）。当它们是具有一组共同值的枚举时尤其如此。
#6
1
For example, your database has tables which store information about Sales and Human resource departments, you could name all your tables related to Sales department as shown below:
例如，您的数据库具有存储有关销售和人力资源部门的信息的表，您可以命名与销售部门相关的所有表，如下所示：
SL_NewLeads SL_Territories SL_TerritoriesManagers
SL_NewLeads SL_Territories SL_TerritoriesManagers
You could name all your tables related to Human resources department as shown below:
您可以命名与人力资源部门相关的所有表格，如下所示：
HR_Candidates HR_PremierInstitutes HR_InterviewSchedules
HR_Candidates HR_PremierInstitutes HR_InterviewSchedules
This kind of naming convention makes sure, all the related tables are grouped together when you list all your tables in alphabetical order. However, if your database deals with only one logical group of tables, you need not use this naming convention.
这种命名约定可以确保，当您按字母顺序列出所有表时，所有相关表都会组合在一起。但是，如果数据库仅处理一个逻辑表组，则无需使用此命名约定。
Note that, sometimes you end up vertically partitioning tables into two or more tables, though these partitions effectively represent the same entity. In this case, append a word that best identifies the partition, to the entity name
请注意，有时您最终会将表垂直分区为两个或更多表，尽管这些分区实际上代表了同一个实体。在这种情况下，请将最能标识分区的单词附加到实体名称
#7
1
Actually, there is a reason for that kind of naming, especially when it comes to fields, you're likely to join on. In MySQL at least, you can use the USING keyword instead of ON, then users u JOIN posts p ON p.user_id = u.id becomes users u JOIN posts p USING(user_id) which is cleaner IMO.
实际上，这种命名是有原因的，特别是涉及领域时，你可能会加入。至少在MySQL中，你可以使用USING关键字而不是ON，然后用户你加入帖子p ON p.user_id = u.id成为用户你加入帖子p USING（user_id）这是更清洁的IMO。
Regarding other types of fields, you may benefit when selecting *, because you wouldn't have to specify the list of the fields you need and stay sure of which field comes from which table. But generally the usage SELECT * is discouraged on performance and mainenance grounds, so I consider prefixing such fields with table name a bad practice, although it may differ from application to application.
关于其他类型的字段，在选择*时可能会受益，因为您不必指定所需字段的列表，并确保哪个字段来自哪个表。但一般来说，在性能和维护性方面不鼓励使用SELECT *，所以我认为在这些字段前加上表名是一种不好的做法，尽管它可能因应用程序而异。
#8
0
Sounds like the conclusion is: If the field name is unique across tables - prefix with table name. If the field name has the potential to be duplicated in other tables, name it unique.
听起来像结论是：如果字段名称在表间是唯一的 - 前缀与表名称。如果字段名称可能在其他表中重复，请将其命名为唯一。
I found field names such as "img, address, phone, year" since different tables may include different images, addresses, phone numbers, and years.
我找到了诸如“img，address，phone，year”之类的字段名称，因为不同的表格可能包含不同的图像，地址，电话号码和年份。
#9
0
We should define primary keys with prefix of tablename.
我们应该用tablename的前缀定义主键。
We should use use_id instead if id and post_id instead of just id.
如果id和post_id而不是id，我们应该使用use_id。
Benefits:-
优点： -
1) Easily Readable
1）易于阅读
2) Easily differentiate in join queries. We can minimize the use of alias in query.
2）轻松区分联接查询。我们可以在查询中最小化别名的使用。
user table : user_id(PK)
用户表：user_id（PK）
post table : post_id(PK) user_id(FK) here user table PK and post table FK are same
post表：post_id（PK）user_id（FK）这里用户表PK和post表FK是一样的
As per documentation,
根据文件，
3) This way we can get benefit of NATURAL JOIN and JOIN with USING
3）这样我们可以通过USING获得NATURAL JOIN和JOIN的好处
Natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard. The goal was to align the syntax and semantics of MySQL with respect to NATURAL JOIN and JOIN ... USING according to SQL:2003. However, these changes in join processing can result in different output columns for some joins. Also, some queries that appeared to work correctly in older versions (prior to 5.0.12) must be rewritten to comply with the standard.
USING的自然连接和连接（包括外连接变体）根据SQL：2003标准进行处理。目标是根据SQL：2003将NATURAL的语法和语义与NATURAL JOIN和JOIN ... USING对齐。但是，联接处理中的这些更改可能会导致某些联接的输出列不同。此外，一些似乎在旧版本（5.0.12之前）中正常工作的查询必须重写以符合标准。
These changes have five main aspects:
这些变化有五个主要方面：
1) The way that MySQL determines the result columns of NATURAL or USING join operations (and thus the result of the entire FROM clause).
1）MySQL确定NATURAL或USING连接操作的结果列的方式（以及整个FROM子句的结果）。
2) Expansion of SELECT * and SELECT tbl_name.* into a list of selected columns.
2）将SELECT *和SELECT tbl_name。*扩展为所选列的列表。
3) Resolution of column names in NATURAL or USING joins.
3）在NATURAL或USING连接中解析列名。
4) Transformation of NATURAL or USING joins into JOIN ... ON.
4）将NATURAL或USING连接转换为JOIN ... ON。
5) Resolution of column names in the ON condition of a JOIN ... ON.
5）在JOIN ... ON的ON条件下解析列名。
Examples:-
例子：-
SELECT * FROM user NATURAL LEFT JOIN post;
SELECT * FROM user NATURAL JOIN post;
SELECT * FROM user JOIN post USING (user_id);