Many to many relation tables

In business applications you often need many-to-many relations for storing relations between business objects.

Example

Imagine a library software in which we want to store books. A single book may have one or multiple authors. On the other hand one author may have written one or multiple books. In this case, next to the tables books and authors, we need a third table for storing the relations between authors and books. Every record in that relation table defines there is a relation between one author and one book. There may be multiple relations for each author and for each book.

For many-to-many relation we recommend the following naming pattern:

  • Table1Singular + _ + Table2Plural

Examples

  • author_books
  • book_authors

Minimum requirements

If we don’t need to store additional information but just the relation itself, we only need three columns

  1. a primary key column for each relation, named id
  2. a foreign key column related to the primary key of the first table
  3. a foreign key column related to the primare key of the second table

Naming conventions

Let’s assume our business is car-rental. We already have tables named cars and customers. A customer can rent multiple cars over the time. And a car may have been rented by different customers over the years.

  • car_customers (singular_plural) recommended
  • car_customer (singular_singular)
  • cars_customers (plural_plural)

Of course you can also name the relation table other way round:

  • customer_cars (singular_plural)
  • customer_car (singular_singular)
  • customers_cars (plural_plural)

There is no right or wrong. It does not matter which pattern you choose, but you should keep it and stick to your decision, once made.

Sometimes there is a well known term for a relation. In this case, for example, we know for this kind of relation there is the term "contract". So depending on your application you may also name the relation table contracts (plural). See next chapter.

Caution
Your application will probably grow. Later there may be contracts between different business objects, for example contracts between companies and employees. Be careful when you are using common, abstract names for tables, because there may be naming collisions later.

Additional columns

In this case we’d like to store more information than just the relation itself. For example we need a contract-number, the date of contract, the begin- and end-dates of the contract, a payment-status, perhaps a PDF scan of the signed contract and so on.

As you can see, we already have a good name for our table: contracts. In contracts we are going to store which customer is going to rent or has rented which car. contracts needs a foreign key column towards customers AND a second foreign key column towards cars as mentioned above.

Once again: there is no right or wrong. You can also call the contracts table car_customers or customer_cars.

Do you like it?