Pros and Cons of having additional columns in join table of many to many relationship

Question

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?

Answer 1

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.