Many to Many Association Tables - Is it customary to put additional columns in these tables?

Question

We've encountered the following situation in our database. We have table 'A' and table 'B' which have a M2M relationship. The association table is named 'AB' and contains a FK column to table 'A' and a FK column to table 'B'. Now we've identified a need to store additional data about this association. For example, a date when the association occurred, and who made the association etc. We've decided to put these additional columns in the 'AB' association table. However, something tells me this is frowned upon by database purists. On the other hand, it makes no sense to us to create yet an additional table to store this associated data.

What's the prevailing thought on this?

Answer 1

If the data actually pertains to the relationship rather than one of the individual entities...then yes, put it in the pivot table.

Answer 2

I see nothing wrong with that at all. If the information is regarding the association itself it seems the absolute correct place to store it.

If you would create a new table to store this in, it would just relate to the associaton table anyways one to one. This essentially would just be extending the association table.

Answer 3

It makes the most sense to store data about the association in the association table itself. I've never heard of anyone frowning upon this method... it will actually speed things up over storing this info in a separate table, because it will save you a join to retrieve it.

Answer 4

I've done this before and don't think there is anything wrong with it. If the data has to do with the relationship, like in your case where it is the date that the association occurred, and doesn't belong specifically to one table or the other, the association table is the place to put it.

Answer 5

In this design, you're treating the association itself as an entity. If that real-world entity, the relationship between the other two entities, has its own attributes, then the table representing that relationship should also represent the attributes of that relationship.

If you created a separate table, what real-world entity would you be modeling with that table? Ancillary information related to but not directly a part of the relationship between two entities? That's hard to conceptualize, doesn't seem to make much design sense, and almost certainly would perform worse.

Answer 6

"However, something tells me this is frowned upon by database purists."

I cannot imagine any person who is knowledgeable in database design and who would frown at your design, if that design really is an accurate model for the reality that you're dealing with.

Answer 7

In this recent answer, I advocate a link table instead of a simple FK relationship. One of the benefits is when the relationship starts to have more and more data associated with it so that it becomes an entity. Specifically with effective dates and changes even in a simple many-to-one hierarchy, it can still make a lot of sense to use a many-to-many link table design. You've obviously already had a need for the link table - now it's clear that other attributes exist attached to that relationship.

Of course, it turns out these are the right place to put these attributes; you would probably experience some difficulties putting them in either table, either with normalization or just plain incorrect semantics. This also gives good options for indexing, since you can add an index on the effective date or relationship status to this relatively narrow table without deciding which indexes it might need to be added to in the other entity tables

I would not think any experienced database designer would object if you showed the design and how it's design features and advantages were used in practice. To me, it would not need extensive design justification.