SQL Join Table Naming Convention

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  

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.

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.

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

• 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

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

Here are some I do know:

• employee reports to line manager == org chart

• student takes course == enrolment

• woman marries man == marriages

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

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

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

This is the convention at my workplace:

UsersXRoles

I've been thinking carefully about this, and I would link the table User and the table Role with 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.

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.

2 approaches:

1. where you will only ever have one relationship between the tables: join table could be RoleUser or Role_User. Go alphabetic with your name order, Role 1st, then User, then you don't have to try to remember!

2. where you will have multiple relationships between the tables: relationshipname - e.g. you might have a list of regular roles for users, and you might have a list of potential, or past roles for users. Same 2 tables, but different relationships. Then you might have RoleUser_Current and RoleUser_Past.

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

User
Role
User2Role

RoleUser - I use alphabetic ordering (i.e. Role comes before User). That way when you're writing a query you won't have to try and remember which order you used to name the join table.

I also use singular as someone else mentioned - you don't have to try to remember! Works for me.

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'.

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

I try to keep things simple, but also be descriptive:

user_role_join

Its good to name join table by using names of tables which it connects. For example two tables "products" and "product_tags", and the joining table is called "product_product_tags". The big advantage is that from this name you can immediatelly say which tables its joining together. When you have 50 and more tables in your DB its good to have it like this and you no longer need to think about joining tables purposes.

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

e.g. SELECT user.id AS user_id