In business applications you often need many-to-many relations for storing relations between business objects.
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
authors, we need a third table for storing the relations between
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:
If we don’t need to store additional information but just the relation itself, we only need three columns
- a primary key column for each relation, named
- a foreign key column related to the primary key of the first table
- a foreign key column related to the primare key of the second table
Let’s assume our business is car-rental. We already have tables named
customers. A customer can rent multiple cars over the time. And a car may have been rented by different customers over the years.
Of course you can also name the relation table other way round:
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.
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.
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 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