In various open source CMS, I have noticed that there is a separate table for mapping two relational tables. Like for categories and products, there is a separate product_category_mapping table. This table just has a primary key and two foreign keys from the categories and product tables.
My question is what are the benefits of this database design rather than just linking the tables directly by defining a foreign key in either table? Is it just matter of convenience?
Such a table is often referred to as a link table or bridge table.
It is the standard way of creating a many-to-many relationship. With a direct foreign key between the two tables, you can only create a one-to-many relationship (because the primary key that the foreign key points at is also a unique constraint), or a one-to-one relationship (if the foreign key itself is also a unique constraint).
Depending on the DB design philosophy, the primary key column is often left out, and the primary key of the bridge table is composed of the two foreign key columns themselves (you need a unique constraint on these columns anyway, so why not make it the primary key?).
That's an easy way to implement many-to-many relationships.
Consider this two tables:
category
--------
categoryID [PK]
categoryName
product
-------
productID [PK]
productName
If you add a categoryID field to product, every product can have only one category. But if we have a product_category_mapping like this:
product_category_mapping
------------------------
mappingID [PK]
productID [FK]
categoryID [FK]
then we can have:
mappingID productID categoryID
--------------------------------
1 1 1
2 1 2
3 2 3
4 2 1
So product 1 is of category 1 & 2 and product 2 is of category 3 & 1, so many products belong to many categories, and many categories have many products.
As tdammers writes, this table is often referred to as a link table or bridge table, and I've even seen it referred as a HABTM table, from HasAndBelongsToMany which is apparently Ruby on Rails speak for many to many. And Wikipedia calls it a junction table and has quite a few more names for it.
mappingID key, one common scenario is to better keep track of massive import / exports where the junction table holds a lot of foreign keys.
The reason to use a mapping table is to eliminate duplication. Go ahead and try some other mapping technique. You won't be able to prevent duplicate data without it.
And that brings a 2cd question. Why bother to eliminate duplication? So you only have to edit data 1 time in 1 place. Sometimes there are performance penalties for eliminating duplication. Other times there are performance INCREASES for eliminating duplication. For example it's faster to fill a drop down list with normalized lookup values, than selecting distinct duplicate values in a big table.
