We have experienced the next situation within our database. We now have table 'A' and table 'B' that have a M2M relationship. The association table is known as 'AB' and consists of a FK column to table 'A' along with a FK column to table 'B'. Now we have recognized a necessity to keep additional data relating to this association. For instance, to start dating ? once the association happened, and who made the association etc. We have made the decision to place these extra posts within the 'AB' association table. However, something informs me this really is frowned upon by database purists. However, it can make no sense to us to produce yet one more table to keep this connected data.
What is the prevailing deliberation over this?
I see nothing wrong with this whatsoever. When the details are concerning the association itself it appears the complete correct spot to store it.
Should you produce a new table to keep this in, it might just connect with the associaton table anyways face to face. This basically would certainly be stretching the association table.
I have carried this out before and do not think there's anything wrong by using it. When the data has related to the connection, as with your situation where it's the date the association happened, and does not belong particularly to 1 table or even the other, the association table is where to place it.
When the data really relates towards the relationship instead of among the individual organizations...then yes, place it within the pivot table.
It can make probably the most sense to keep data concerning the association within the association table itself. I have never heard about anybody frowning this method... it'll really quicken things over storing this data inside a separate table, because you will save a join to retrieve it.
Within this design, you are dealing with the association itself being an entity. In the event that real-world entity, the connection between your other two organizations, features its own characteristics, then your table representing that relationship also needs to represent the characteristics of this relationship.
Should you produced another table, what real-world entity will you be modeling with this table? Ancillary information associated with although not directly an element of the relationship between two organizations? That's tough to conceptualize, does not appear to create much design sense, and probably would perform worse.
"However, something informs me this really is frowned upon by database purists."
I am unable to imagine anyone who's knowledgeable in database design and who'd frown at the design, in the event that design is really a precise model for the matter that you are coping with.
In this recent answer, I advocate a hyperlink table rather than an easy FK relationship. One benefit happens when the connection begins to possess increasingly more data connected by using it to ensure that it is really an entity. Particularly with effective dates and changes even just in an easy many-to-one hierarchy, it may still make lots of sense to utilize a many-to-many link table design. You've clearly already had an excuse for the hyperlink table - now it's obvious that other characteristics exist mounted on that relationship.
Obviously, it works out fundamental essentials right spot to put these characteristics you'd most likely experience some difficulties putting them either in table, either with normalization or simply plain incorrect semantics. This gives good choices for indexing, since you can include a catalog around the effective date or relationship status for this relatively narrow table without determining which indexes it should be included to within the other entity tables
I'd not think any experienced database designer would object should you demonstrated the look and just how it's design features and advantages were utilised used. In my experience, it wouldn't need extensive design justification.