SQL Join Table Naming Convention [closed]

Solution 1:

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.

Solution 2:

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.

Solution 3:

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

Solution 4:

• 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