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?
Best Answer
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?).