SQL Join Table Naming Convention

Question

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.

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

Answer 1

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

Answer 2

Indeed, use table aliases and select the columns with same names apart with the AS selector.

e.g. SELECT user.id AS user_id

Answer 3

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

By the way, the pk Id is not really necessary in a join table. Better make the UserId and RoleId together the pk or just uk (unique key) so that you can ensure unique User-Role relationships.

Answer 4

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

Answer 5

We've always used the names of the two tables followed by the word, 'Links'. So in your example our table name would be 'UsersRolesLinks'.

Answer 6

You could steal a page from Microsoft, and call it UsersInRoles.

Answer 7

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

Answer 8

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.

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

Answer 9

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

Answer 10

I have a convention which I find easy to see right away:

User
Role
User2Role

Answer 11

This is the convention at my workplace:

UsersXRoles

Answer 12

• Table names should always be singular, that way later you don't have to be like "is it User or 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 UserRole makes much more sense than RoleUser.
• I've seen things like User_X_Role or UserXRole as well, if you like the extra verbosity