Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

up vote 7 down vote accepted

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

share|improve this answer
    
ohh k....So in short, If i need to have many-to-many relationship, one must follow this approach, right ?? –  Pankaj Upadhyay Nov 13 '11 at 11:34
    
@PankajUpadhyay: Correct. M-M relationships are to be implemented in this way. –  Emmad Kareem Nov 13 '11 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 '11 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). –  Emmad Kareem Nov 13 '11 at 13:13
    
@EmmadKareem : ya , i saw that....My requirements are one-to-many, so i won't be creating the table....Thanks mate –  Pankaj Upadhyay Nov 13 '11 at 13:15
add comment

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.

share|improve this answer
    
It's not just Rails-speak, virtually all of the PHP frameworks I've used refer to it as HABTM. –  sevenseacat Nov 13 '11 at 12:28
    
@Karpie Some examples? I know CakePHP uses the HABTM name, but it did start out as a Rails clone. –  Yannis Rizos Nov 13 '11 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 '11 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 Rizos Nov 13 '11 at 13:15
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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