Should a many-to-many relationship define anything other than the relationship

Question

Taking the typical products / categories many-to-many relationship you'd typically have a relationship like follows:

table -- ProductsCategories
column - ProductId
column - CategoryId

Is it ill advised to add other properties to that relationship, things that only exist on that specific relationship. Such as (please forgive my terrible examples, I'm not good at examples):

table -- ProductsCategories
column - ProductId
column - CategoryId
column - DiscountForProductsInThisCategory
column - CategoryImageForProductsInThisCategory

Answer 1

A many to many relationship is simply a table with 2 one to many relationships, think of it as 3 tables with 2 relationships rather than 2 tables with a relationship and data associated with it. There is no difference in the implementation.

Either way it's perfectly acceptable.

Answer 2

I think it is totally fine to add other fields to this table. One example from things I have done is that I usually add a timestamp field so that I know when the relationship was created between the two entites.

Answer 3

Of course you can store this information there. Anywhere else and it wouldn't be normalized.

Answer 4

Unless the data is specifically for that particular relationship, then avoid this, as you will end up duplicating a lot of data. In your example, the images are linked to the category, and so should be stored there. However, a timestamp indicating when the relationship was made and edited, as well as user-ids that created and modified the relationship, should be in that table.

Answer 5

Yes.

A relationship can have attributes, IMO. Although some design masters don't think so.

Consider the "enrolled" relationship between Students and Courses. If a student could be enrolled either for credit or as an auditor, it would make sense for this attribute to be stored in a table whose key is (StudentID, CourseID). This table is the relationship table that catalogues which students are enrolle d in which courses.

Answer 6

For example, if you have a table of Athletes and a table of Races. The join table AthleteRace represents that athlete's participation in that race, so you could have their race time in there as well.

The two exapmles you have:

column - DiscountForProductsInThisCategory
column - CategoryImageForProductsInThisCategory

belong in the Category table, as they relate to a Category, not to the membership of a product within a category.