How to handle a composite ID in a many-to-many?


#1

I am wondering how you represent a many-to-many relationship that is supported by a join table. Here is a
link to a clear example. There is a stock table, category table, and a stock_category table to join them. A stock can have many categories and vice versa. So the join table has two ids, a stock_id foreign key to the stock table and a category_id foreign key to the category table. How is this represented as a relationship?

The other important part of this table structure is that the stock_category table also has additional attributes. It is its own resource in that sense. This part of it is similar to these two* questions. I suppose that since it is its own resource it isn’t many-to-many anymore. A stock has many stock_category, but a stock_category has only one stock.

Either way I am quite confused on the whole topic.

*Edit, as a new user I can only put two links in a post. here is the 3rd: Metadata About Relationships


#2

What (roughly) are the other attributes on your join table? Knowing the role of those attributes would give me a better sense of how you might best model this.


#3

You can solve this by adding an additional column to the reference table and set the primary key on this column (as well as a unique constraint for the other two). Perhaps you can also use a hidden id column for this (like OID in Postgres).

IMHO every table should have a one column primary, because it simplifies foreign key usage, working with IN lists, referencing, the later extension of the database structure etc. Many ORMs require this also.