How to name an association table connecting two tables having common name

Question

I have two tables products and product_tags in my database having a many-to-many relationship. But I am having a hard time coming up with a practical name. So I was wondering if there is a standard for such a stuff?

For orders and products which also have many-to-many relationship, I can name the association table like order_items. And it makes sense. There is also people who suggest using xref at the end (like product_tags_xref) but I found some people complaining that it can possibly conflict with autocad or something. Some people suggest repeating the name, but it doesn't seem like a great idea in this case, cos the "products" name repeats. So it would be like products_product_tags which looks ugly and long and also doesn't convey the idea. Also it might be really weird when I am coding in node and have to do something like db.ProductsProductTags which is again ugly.

Right now I am going with product_tag_maps but don't know if its the best naming convention.

I have found questions on this topic in SO and other places but none for this scenario where the name of tables contain same words and repeating the name would be bad.

Also I didn't know where to post the question on DBA or SE. I choose SE because naming matters more when the name is referenced in the code multiple times and the coder might not always have direct reference to the model ER diagram to see whats going on.

Answer 1

There is no generally accepted practice here.

Here a couple of recommendations:

• the preferred approach is to find a name that truly describe what it is from a domain perspective. Your example with order_item clearly belongs to this category. This is extremely clear to everybody and facilitates communication with the stakeholders.
• if you don’t find a right term, it is sometimes possible to adopt a new term that will be understood by everybody including the domain experts. It still facilitates the communication.
• in last resort, just concatenate both tables. This is better than inventing a term that could be confusing or misleading. You may in some cases factor-out some redundant terms. But do this with extreme care because of the risk of collision (including in future): product_product_tag is ugly but clearly tells what it is about.
• avoid fancy suffixes: it only makes name longer without really adding value compared to the previous approaches. Use this only if forced (e.g. if some engine uses the suffix to automatically navigate across the data).

Answer 2

Why not simply "product_tagging" to cover the N-to-N relation? You've used nouns for objects, you can use verbs for N-to-N relations linking them (and in English as it's not easy to distinghuish nouns and simple forms of verbs you often need a verb variant). You could also use the "and" joiner: "products_and_tags".

You may also reduce these names by avoid the plural (the extra 's'), you name only the type of each individual row member of that table and it is frequently simpler to read in SQL requests for terminal objects, in that case you can use the plural only for N-to-N relation tables (containing only unique object identifiers and no other attributes, except possibly a qualifier from a finite set if you've merged several N-to-N relations into the same table, something that is usually done for RDF-like triples in data stores).

Other conventions exist where object tables are named with a "_t" suffix (for terminal), while N-to-N relations are named with a "_r" suffix.

And not all projects are using table names in English when these projects are designed for specific markets where they will be decided in another local language. If English is not used, the naming conventions have to be adapted: it's up to each organization to decide about it and document their best practices that facilitates the discussions and documentation of their pojects.

As well you an use camelCase for terminal objects (drop underscores), and use the underscore only for N-to-N relations (works well for binary relations, where each site may often be abbreviated).

For ternary relations (or larger) things get more complicate, and you'll need to be inventive to describe NOT the terminal objects they are linking to, but the verb describing the kind of relations that links them.

In summary: there's no "standard". The only relevant standard is UML-like modeling and correctly describing the cardinalities and referencial constraints (foreign keys) and life cycles of objects (such as recursive deletion, or archiving?)