SQL Join Table Naming Convention

Question

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.