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


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


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.


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.