Should I use an index column in a many to many "link" table?

Question

I have two tables, products and categories which have a many to many relationship, so I'm adding a products_categories table which will contain category_id and product_id.

Should I add another (auto-incrementing) index column or use the two existing ones as primary key?

Answer 1

That depends.

• Are you seeing your data more as set of objects (and relational database is just a storage medium) or as set of facts represented and analyzed natively by relational algebra.

• Some ORMs/Frameworks/Tools don't have good support for multicolumn primary keys. If you happen to use one of them, you'll need additional id column.

• If it's just a many-to-many relationship with no additional data associated with it, it's better to avoid additional id column and have both columns as a primary key.

• If you start adding some additional information to this association, then it may reach a point when it becomes something more then many-to-many relationship of two entities. It becomes an entity in it's own right and it'd be more convenient if it had it's own id independent to entities it connects.