Are there any naming conventions for self-referential, many-to-many relationship tables?

Let’s say we have a table called Users for a fictional social media platform. In addition to regular users, some more experienced users are asked to be “greeters” where they are assigned other, newer users to greet and encourage. Because greeters aren’t always available, multiple greeters are assigned to each new user and each greeter is assigned multiple new users.

Thus we have a self-referential, many-to-many user relationship we need a link table for.

Are there any conventions for this case?

Here are some ideas based on conventions I’ve found for standard many-to-many tables:

• user_user: doesn’t tell us much and there might be more than one relationship like this
• Greeters: (i.e. use a new name that describes the relationship) pretty good, but it doesn’t communicate that it’s a link table or related to Users
• greeter_user: (use a different name for one part of the relationship)

What do you use or what would you recommend?

Answer

In the entity–relationship model this would be modeled as user encourages user.

This could be translated into the tables Users and Encouragements.

CREATE TABLE Users (
   username VARCHAR(32) PRIMARY KEY,
   ...
);

CREATE TABLE Encouragements (
   greeterName VARCHAR(32),
   newbieName VARCHAR(32),
   PRIMARY KEY (greeterName, newbieName),
   FOREIGN KEY (greeterName) REFERENCES Users(username),
   FOREIGN KEY (newbieName) REFERENCES Users(username)
);

https://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model