SQL 连接表命名约定

SQL Join Table Naming Convention

提问by Josh Close

I have 2 tables: Users and Roles, and I have a table that joins these together. The only thing in the join table is Ids that link the 2 tables.

我有 2 个表：用户和角色，我有一个将这些连接在一起的表。连接表中唯一的东西是链接 2 个表的 ID。

What should I call this table? I've never really seen a good naming convention for this.

我应该怎么称呼这张桌子？我从来没有真正见过一个好的命名约定。

Conventions I've seen before:

我以前见过的约定：

• UsersToRolesJoin
• UsersToRolesLink
• UsersToRolesMembership
• UsersRoles

• 用户到角色加入
• 用户到角色链接
• 用户到角色成员资格
• 用户角色

Ex:

前任：

Users:  
Id  
Name

Roles:  
Id  
Name  

TableThatJoinsTheTwo:  
Id  
UserId  
RoleId  

采纳答案by akmad

It seems like the mapping table is storing all the roles that each user is a member of. If this is correct I would call the table UserRoles.

映射表似乎存储了每个用户所属的所有角色。如果这是正确的，我会调用 table UserRoles。

This correctly (IMO) pluralizes the intent of the table rather than UsersRoleswhich just sounds odd.

这正确地（IMO）使表格的意图复数，而不是UsersRoles听起来很奇怪。

回答by BalusC

I'd call the users table User, the roles table Roleand the join table UserRoles.

我会调用用户表User、角色表Role和连接表UserRoles。

By the way, the pk Idis not really necessary in a join table. Better make the UserIdand RoleIdtogether the pk or just uk (unique key) so that you can ensure unique User-Rolerelationships.

顺便说一下，pkId在连接表中并不是真正必要的。更好地使UserId与RoleId一起PK，或只是英国（唯一键），这样就可以保证独特的User-Role关系。

回答by J__

I would suggest simply UsersRoles, as that is what it holds.

我会建议简单的UsersRoles，因为这就是它所拥有的。

回答by adamJLev

• Table names should always be singular, that way later you don't have to be like "is it Useror Users? What about things that end in an S? etc" (I would change this now if you just started the project)
• The common convention would be: User, Role, and the xref table: UserRole.
• The most important table, or the one that existed before goes first. This is specially true if the 'role' table has no real use outside user permission stuff. so UserRolemakes much more sense than RoleUser.
• I've seen things like User_X_Roleor UserXRoleas well, if you like the extra verbosity

• 表名应该总是单数，这样以后你就不必像“是它User还是Users？以 S 结尾的东西怎么样？等”（如果你刚开始这个项目，我现在会改变这个）
• 共同的约定是：User，Role，和外部参照表：UserRole。
• 最重要的表，或者之前存在的表放在第一位。如果“角色”表在用户权限之外没有真正的用途，则尤其如此。所以UserRole比RoleUser.
• 我见过类似的东西User_X_Role或者UserXRole为好，如果你喜欢额外的冗长

回答by Quassnoi

I'd call the link table this:

我将链接表称为：

Remove_The_Surrogate_Primary_Key_From_The_Link_Table_Unless_You_Can_Prove_That_You_Really_Need_One

回答by onedaywhen

The database represents am enterprise, right? So what do the people in that enterprise call the relationship?

数据库代表企业，对吗？那么，那个企业的人怎么称呼这种关系呢？

Here are some I do know:

以下是我所知道的一些：

• employeereports to line manager== org chart

• studenttakes course== enrolment

• womanmarries man== marriages

• employee向line manager== 组织结构图报告

• student需要course==注册

• woman结婚man== 结婚

When in doubt, ask a domain expert within the enterprise.

如有疑问，请咨询企业内的领域专家。

回答by Adriaan Stander

We have the same structure and call the link table UserRoles.

我们有相同的结构并调用链接表 UserRoles。

回答by Ewen Cartwright

This is the convention at my workplace:

这是我工作场所的惯例：

UsersXRoles

回答by Jez

I've been thinking carefully about this, and I would link the table Userand the table Rolewith the table UsersRoles. I think its nice, because it indicates that the many-to-many relationship could be considered as linking many roles to one user, or indeed many users to one role (so both being plural makes sense). It can also be read as "Users' roles", indicating that the normal way of thinking about the relationship is the "roles that a user has" way round.

我一直在仔细考虑这个问题，我会将 tableUser和 tableRole与 table链接起来UsersRoles。我认为这很好，因为它表明多对多关系可以被视为将多个角色链接到一个用户，或者实际上将多个用户链接到一个角色（因此两者都是复数是有道理的）。它也可以读作“用户的角色”，表示通常的思考关系的方式是“用户拥有的角色”。

回答by Alex Gandy

I've always gone with something like : rel_user_rolesor relUserRoles. Which table goes first usually just depends on where it is in the data model.

我总是使用类似 :rel_user_roles或relUserRoles. 哪个表先出现通常只取决于它在数据模型中的位置。