Jens Dahl Møllerhøj

March 31, 2021

How to Name your Junction Tables

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:

advertisement
announcement
arrangement
assignment
attachment
commitment
ejectment
employment
enrollment
enrollment
measurement
involvement
payment
placement
publishment
reimbursement
shipment

Ending in -ship:

authorship
membership
ownership
partnership
relationship

Ending in -tion:

affiliation
association
connection
estimation
participation
selection
junction
transaction
prediction

Ending in -sion

Decision

Ending in -ling:

coupling
revealing
tangling

Ending in -ing:
(When possible, prefer other endings, e.g. -ment, -tion,  -ship)

belonging
booking
documenting (prefer documentation)
engaging
grouping
handling
meeting
offering
opening
painting
picking
sampling
shipping (prefer shipment)
showing
troubleshooting

Ending in -ry:

expiry
discovery
memory
summary
injury
salary
documentary

Ending in -al:

Approval
Withdrawal

Ending in -ance:

Appearance

Ending in -ence:

Other:

badge
bond
chip
choice
contract
draft
hookup
link
option
pick
pin
seam
tie
commit