1

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?

7

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.

  • +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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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