In relational database design, it is a well-known design limitation that many-to-many relations between two tables cannot exist without an intermediate table connecting the two. For example, an Author can write many Books, and a Book can have many Authors:
This relationship, sometimes referred to as a "Has And Belongs To Many" (HABTM) relationship, imposes a requirement upon the database designer to introduce a new database table:
It is a common naming convention for such tables to join the singular name of both connected tables, resulting in names such as "Author_Book", or "Book_Author".
Unfortunately, this naming convention often hides important underlying concepts. Discovering a descriptive, "real-world" name for junction tables will often increase the structure and readability of application code.
In the example above, a descriptive table name might be "Authorship". A Book is then the result of multiple Authorships, and an Author is the creator of multiple Authorships.
In the future, the application might need to register whether a Book is the first, second, or 42'nd work of an Author, or perhaps denote what percentage the Author's contribution to the Book was (i.e., 10%, 50%, 90%)? These concepts are intuitively represented as "numbering" and "contribution" columns on the Authorship model. A name such as "Author_Book" does not help to discover these names, possibly resulting in less readable code.
If this is the case, then why is the "Author_Book", or "Book_Author" naming convention still so common? Unfortunately, finding the underlying concept of junction tables can be quite challenging, and while no silver bullet exists, it can be a helpful exercise to think about what the creation of a record in this table requires in the real-world. In the above example, an author must write a book before the connection can exist. Had, for example, the relationship existed between the Book and its Publisher, we might have called it a "Publishment".
Interestingly, the English language often uses endings such as -ment or -ship for junction table concepts. These words have come into existence to fulfil the need to express a given relationship between two nouns with a new, concrete noun of its own. Sometimes, no English noun exists, and programmers will have to come up with their own imaginary nouns to name a junction table, such as "groupment", “belongship”, or “paintment”.
To help illustrate my point and aid fellow programmers, I've started a list of possible names for junction tables. It might serve as a starting point when attempting to find the underlying concept of a junction table. Feel free to contribute to this with your own suggestions :)
Ending in -ment:
Ending in -ship:
Ending in -tion:
Ending in -sion
Ending in -ling:
Ending in -ing:
(When possible, prefer other endings, e.g. -ment, -tion, -ship)
documenting (prefer documentation)
shipping (prefer shipment)
Ending in -ry:
Ending in -al:
Ending in -ance:
Ending in -ence: