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?

3 Answers 3


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

  • ohh k....So in short, If i need to have many-to-many relationship, one must follow this approach, right ?? Nov 13, 2011 at 11:34
  • 1
    @PankajUpadhyay: Correct. M-M relationships are to be implemented in this way.
    – NoChance
    Nov 13, 2011 at 11:44
  • @PankajUpadhyay: in theory, you can use other approaches, but they're messy and wrong, so I suggest you don't ever go there.
    – tdammers
    Nov 13, 2011 at 11:56
  • 2
    @PankajUpadhyay: If you want to create a Normalized database, you have to use a 3rd table as explained. If you have 1-M relationship, you definitely don't want a 3rd table. If you have finite and limited M-M and don't care about Normalization, you can do without a 3rd table. In general, stick to a normalized database unless you are building a data warehouse or a data mart (in such cases, a normalized schema is debatable).
    – NoChance
    Nov 13, 2011 at 13:13
  • @EmmadKareem : ya , i saw that....My requirements are one-to-many, so i won't be creating the table....Thanks mate Nov 13, 2011 at 13:15

That's an easy way to implement many-to-many relationships.

Consider this two tables:

categoryID [PK]

productID [PK]

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:

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.

  • It's not just Rails-speak, virtually all of the PHP frameworks I've used refer to it as HABTM. Nov 13, 2011 at 12:28
  • @Karpie Some examples? I know CakePHP uses the HABTM name, but it did start out as a Rails clone.
    – yannis
    Nov 13, 2011 at 12:29
  • I'm a big fan of surrogates. But I prefer to leave out the extra surrogate key "mappingID" and put a composite PK on "productID" and "categoryID". In my mind composite key composed of 2 surrogates is a surrogate in it's own right. You will never actually use the surrogate "mappingID" in any join.
    – Lord Tydus
    Nov 13, 2011 at 13:08
  • @LordTydus I didn't write of the composite key as tdammers did, no point to recycle his answer. But there are valid uses for the mappingID key, one common scenario is to better keep track of massive import / exports where the junction table holds a lot of foreign keys.
    – yannis
    Nov 13, 2011 at 13:15

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.