I've seen a few different naming schemes throughout multiple languages when it comes to many-to-many relationships, so I was wondering: what naming scheme do you use for naming these relationships? Please include the platform for which you are developing.
Use Posts and Tags as examples for the tables that would be related.
Examples:
In MSSQL, I use names of the form:
Posts_Tags_Link
I like this because it clearly indicates you're dealing with a relationship table, and it calls out both tables you're dealing with.
I've never had a strict convention for this, but in the instance of Users and Roles it would be Users_Roles with the "dominant" table taking place on the left.
If I have a table 'posts' and a table 'tags' I call my relationship table:
posts_tags_rel
Preferring camelcase and singular rather than plural, I would use e.g. PostTag and UserRole for the examples given. The primary reason is that this is the most natural way to express the content of the table in spoken or written language.
Equivalents would be post_tag and user_role.
Thus I can speak sensibly of a specific UserRole, UserRole records (or UserRoles, to abbreviate a multiple).
In this way the domain sense of the terms is reinforced over the technical sense, and it's easier to speak naturally with stakeholders who are not techies.
I developed my table naming scheme over the years (MSSQL databases), so my current guidelines may change "without notice":
Tables are named in singular (except for SQL keywords, such as Roles or Users), camelcase where needed. I switched from earlier Table_ Type (typing records in table Table) to TableType.
n:m relations are named Table1_ Table2. Therefore my solution would be Post_Tag.
I guess the only relevant thing is that you pick a naming scheme and stick to it throughout a project.
I would name the tables in question as follows:
Post
Tag
PostTags
Typical naming conventions for tables suggest that you use the singular in your table name. Not Posts, but Post. That's subjective, of course, but I think it's currently prevailing opinion. It's certainly mine, regardless. :)
That said, at my current place of employment, I would be forced to name PostTags as PostTagsXref. Why? Simply because the DB guys are afraid that anyone not familiar with a weak/associative entity won't understand why the table doesn't have a single auto-incrementing primary key.
*post_per_tag*
Also this enforce to name all entities in singular.
Continuing with the example of Users and Roles, I like UserRoles or User_Roles.
I do not like the singular use. Your table is for associating many roles to a user so say it like it is - plural.
For the primary table names I also like to stick with plural names because the tables contain multiple records. "Users" makes more since than "User" because you have multiple users defined within the table's data.
Must sound good in English:
posts
tags
post_tags
users
roles
user_roles
I've seen and used posts_x_tags and posts_2_tags
I prefer the first purely for aesthetic reasons, and I prefer these forms because they don't imply directionality (as "per" does for example).
I tend to do something more natural language-oriented, like name it users_have_roles or something along those lines.
I use camelcase, singular names separated by a "cross". For example:
User_x_Role
Post_x_Tag
That way you always remember that is a many-to-many table. And the User_Roles would look very similar to UserRoles (if you have other roles such as ApplicationRoles)
I like to use something that describes the data for what it is, rather than just tacking the names together from the two tables. This way it will also make sense should the linking table become more functional at a later time.
In this case, I would go for either PostTags or TagPosts.
We do :
And for definition (lookups):
Not my favorite scheme, but it works out ok as long as your table names aren't crazy. It has the benefit of grouping your AxB tables, but because of that it also hides them out of the way (which is confusing at times.)
But if you didn't do that (prefix), which would you choose: UsersPosts/PostsUsers/UserPost?
Agree mainly with BlackWasp. I think tables should be named descriptively and not simply tacked onto each other. Not only because the linking table can be more functional, but sometimes the relationship doesn't stay as simple as a plain old many to many. There are perfectly good designs where two linking tables are linked together. Do you simply include all four names in your table?
To answer the present example, PostTags makes sense to me because it is the most descriptive.
Also it depends on the db. Strangely, with case sensitive db's such as mysql, I would use post_tags instead. This is in keeping with what I've seen the most common conventions to be in terms of underscores and casing.
I usually name the table to reflect the relationship, since n:m relationships are often not symmetric - usually you're adding one thing to another thing.
For example, if I had your post and tag tables my relationship table would be post_has_tag.
what about double relationships? by example: tables equipment and employee, you could have and employee as a supervisor and an employee as the operator of the machine, what i do is name the relation tables as employee_supervisor_equipment and employee_operator_equipment, what's your opinion?
First try to find the business term that describes the M:N table. The best place to look is in the names of the relationships between the entities in your data model.
CUSTOMER signs CONTRACT
In this example the word signature is a clue that the association between CUSTOMER and CONTRACT is some sort of signature or authorization.
Then only if there is no natural business term for the associative table, try the concatenation methods mentioned in other answers. I only use those concatenation approaches when all else fails, as the business rarely refers to the concepts of the M:N in those terms....but they do understand signatures and authorizations.