Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I searched but did not find any discussions on this topic. Are there any pros and cons on creating many extra columns on joined table? Does it affect speed of data retrieval because we are joining 3 tables?

As far as what I think, it may slow tiny bit but it is necessary otherwise how can we save additional data about many-to-many relationships. Or is there any other way of which I am not aware of?

share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

In general, the wider the table the slower the query. But slower doesn't necessarily mean slow.

The number of joins probably has more to do with the use of id numbers than anything else. Natural keys can speed performance dramatically when the important information is carried in the key. (This happens more often than you might think.)

The M:N table is the natural and sensible place to store information about that M:N relationship. So adding columns to store information about that relationship makes perfectly good sense. Wider tables are usually slower, but narrow tables that don't store data are less useful.

share|improve this answer
    
+1 for natural keys in an intersection table. Also agree that this is the best place to store extra information that applies to the intersection. Depending on the DBMS being used, extra (non-key) columns in the intersection may not slow down joins if all you are doing is joining through to pick up columns from the related tables, since query optimizers often ignore the table columns when they can get away with using a covering index. –  Joel Brown Nov 7 '11 at 2:15
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.