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

Nice question - interested to see what the answers are. Of course, the tables should be called User and Role (not pluralised), but that's a whole nother argument :) — serg10, Nov 19 '09 at 16:16
I generally pluralize table names just because it makes more sense visibly. I don't have a table of user, I have a table of users. — Scott Arrington, Nov 19 '09 at 16:22

score 43 · Accepted Answer · answered Nov 19 '09 at 16:24

43

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.

answered Nov 19 '09 at 16:24

akmad

19,343
2
29
25

3

The table equally describes all roles that a user has as it does all users that a role has. So UsersRoles makes sense. – Ted Henry May 11 '19 at 02:59

BalusC · Answer 2 · 2009-11-19T16:32:53.670

33

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.

edited Nov 19 '09 at 16:32

answered Nov 19 '09 at 16:16

BalusC

1,082,665
372
3,610
3,555

No Primary Key? Why? I though best practice was to always have PK? – Sonny Boy Nov 19 '09 at 16:19
1

@Sonny: Because you're usually never interested in a single row of a join table. It only unnecessarily costs one extra index. – BalusC Nov 19 '09 at 16:21
19

`(User, Role)` make a nice composite `PK` for this table. There is no need in creating a surrogate. – Quassnoi Nov 19 '09 at 16:24
@Balus Even if it's a high-traffic table where the links are changing thousands of times a day? In our app we have many of our links as objects that hold the PK so they can be deleted (or in some rare cases updated). I'm not disagreeing with you, it's just that we never thought of NOT having a PK. :P – Sonny Boy Nov 19 '09 at 16:24
3

`@BalusC`: a join table does need a `PK`, but not a *surrogate* `PK`. What it needs is a composite `PK` made out of the `FKs` it links. – Quassnoi Nov 19 '09 at 16:26
1

@Sonny If you want a primary key, you could simply make a multi-column primary key that spans UserId and RoleId (assuming that one user can't be added to the same role twice :) – Scott Arrington Nov 19 '09 at 16:27
We're talking about a PK in a **JOIN TABLE**. A join table doesn't need a PK. It doesn't represent a real world entity. It just links entities together. You already have keys in flavor of the UserId and RoleId. – BalusC Nov 19 '09 at 16:27
@Quassnoi and Scott: exactly that is what I was trying to tell. – BalusC Nov 19 '09 at 16:28
@BalusC: I would call the relation table 'UserRole' without s given that you suggest the original table names in singular form - for consistency. – van Nov 19 '09 at 16:56
@van: Depends. In our case the `s` at end of table name indicates a join table. – BalusC Nov 19 '09 at 17:08

score 25 · Answer 3 · answered Nov 19 '09 at 16:16

25

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

answered Nov 19 '09 at 16:16

J__

3,777
1
23
31

2

Also remove the 'Id' column unless really required for some reason; Assuming that the lookups are more often done from User side, create a CLUSTERED PK on (UserId, RoleId) and another index on (RoleId, UserId) or just RoleId but including UserId. – van Nov 19 '09 at 16:59

adamJLev · Answer 4 · 2009-11-19T18:01:19.013

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

edited Nov 19 '09 at 18:01

answered Nov 19 '09 at 17:15

adamJLev

13,713
11
60
65

This is how my ORM works (and many others): collections returned are pluralized names of the entity. – D'Arcy Rittich Nov 19 '09 at 17:34

score 10 · Answer 5 · answered Feb 02 '12 at 09:29

10

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.

answered Feb 02 '12 at 09:29

onedaywhen

55,269
12
100
138

2

This is a very good point, and I think it should be used more. For example, 'Enrollment' is crystal clear as to what it is over Student_Course_Link. – Greg Gum Aug 24 '13 at 15:16

score 7 · Answer 6 · answered Nov 19 '09 at 16:20

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

answered Nov 19 '09 at 16:20

Quassnoi

413,100
91
616
614

1

+1 nice one, although there's usually a limit on table name length =) – BalusC Nov 19 '09 at 16:24
Yeah yeah yeah... In my case, I actually don't need it and will be using a composite primary key. – Josh Close Nov 19 '09 at 16:48
`@Josh Close`: Just name your table `UserRole` then :) – Quassnoi Nov 19 '09 at 16:56
...but remove the `_Link` part. – onedaywhen Feb 02 '12 at 09:09
3

While funny and accurate, this doesn't really answer the original question. I much prefer BalusC's answer. And yes, I know this is an old question. – Akrikos Mar 30 '12 at 14:59
@Akrikos: is that extra s that important? – Quassnoi Mar 30 '12 at 20:59
@Quassnoi: I don't really care about the 's' as long as whatever you're doing you do consistently. Although, having an 's' after each of the concatenated names sounds silly to me. – Akrikos Apr 09 '12 at 21:35
In Oracle, I had to name my link tables `Remove_The_Surrogate_Primary01`, `Remove_The_Surrogate_Primary02`, etc. :( – Charles Burns Nov 18 '16 at 23:40

score 6 · Answer 7 · answered Nov 19 '09 at 16:17

6

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

answered Nov 19 '09 at 16:17

Adriaan Stander

162,879
31
289
284

Same here. I prefer plural names for tables (since the users table will typically hold a set of users, not a single user). But when you then link a user to the roles they are in, UserRoles makes sense. – Aaron Bertrand Nov 19 '09 at 16:20
Another good argument might be that many to many might need the Links in the name such that we refer to UserRoleLinks. Might work, but it is up to you X-) – Adriaan Stander Nov 19 '09 at 16:25

score 4 · Answer 8 · answered Nov 19 '09 at 17:09

4

This is the convention at my workplace:

UsersXRoles

answered Nov 19 '09 at 17:09

Ewen Cartwright

15,378
4
22
21

1

I like this and going to use this. Instead of Roles, we have two table named `user` and `user_group`, and calling the link table `user_user_group` kinds of get confusing. `user_x_user_group` looks good! – Phuah Yee Keat Feb 04 '14 at 03:09

score 3 · Answer 9 · answered Oct 04 '12 at 12:51

3

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.

answered Oct 04 '12 at 12:51

Jez

27,951
32
136
233

I like this because it clearly indicates the many:many relationship, when the table names are singular. – Reversed Engineer Jun 13 '18 at 20:20

score 2 · Answer 10 · answered Nov 19 '09 at 16:37

2

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.

answered Nov 19 '09 at 16:37

Alex Gandy

46
4

score 2 · Answer 11 · answered Oct 17 '13 at 03:18

2 approaches:

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

score 1 · Answer 12 · answered Nov 19 '09 at 16:41

1

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

User
Role
User2Role

answered Nov 19 '09 at 16:41

tster

17,883
5
53
72

score 1 · Answer 13 · edited Oct 26 '12 at 09:26

1

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.

edited Oct 26 '12 at 09:26

SchmitzIT

9,227
9
65
92

answered Feb 02 '12 at 02:30

Chalky

11
1

score 0 · Answer 14 · answered Nov 19 '09 at 16:17

0

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

answered Nov 19 '09 at 16:17

Sonny Boy

7,848
18
76
104

score 0 · Answer 15 · answered Nov 19 '09 at 16:18

0

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

answered Nov 19 '09 at 16:18

Matthew Jones

25,644
17
102
155

score 0 · Answer 16 · answered Aug 22 '17 at 01:48

0

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

user_role_join

answered Aug 22 '17 at 01:48

Aperunner

1

score 0 · Answer 17 · answered Apr 12 '18 at 07:52

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.

score -2 · Answer 18 · answered Nov 19 '09 at 16:16

-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

answered Nov 19 '09 at 16:16

Ben Fransen

10,884
18
76
129

SQL Join Table Naming Convention

18 Answers18

Linked