Should I use an index column in a many to many "link" table?
database, database-design, entity-relationship, sql
Solution
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.
Problem
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?