I've two tables, products and categories that have a many to a lot of relationship, so I am adding a products_categories table that will contain category_id and product_id.

Must I add another (autoincrementing) index column or make use of the two existing ones as primary key?

That is dependent.

  • Are you currently seeing your computer data more as group of objects (and relational database is simply a storage medium) or as group of details symbolized and examined natively by relational algebra.

  • Some ORMs/Frameworks/Tools do not have good support for multicolumn primary secrets. If you use one of these, you will need additional id column.

  • Whether it's only a many-to-many relationship without any additional data connected by using it, it's easier to avoid additional id column and also have both posts as primary key.

  • If you wish to then add more information for this association, then it'll become some thing then many-to-many relationship of two organizations. Therefore it becomes entity in it's own and it would be easier whether it had it's own id independent to organizations it connects.

You do not need to include an additional, auto-incrementing index column, however i (possibly unlike most others) still recommend that you simply do. First, it's simpler within the application program to consult a row utilizing a single number, for instance whenever you remove a row. Second, often it works out to become helpful to have the ability to be aware of order where the rows were added.

No, you no longer need whatsoever, considering that both of these posts happen to be performing the purpose of a principal key.

This third column whould just increase the space for your table.

But... You could utilize it's possible to determine an order by which your records where put into your table. This is the only function I can tell for this column.

You don't have to add a car-incrementing index column. Standard practice is by using only the two existing posts as the primary key for M:M association tables as if you describe.

I'd result in the primary key category_id and product_id. Add a car increment only when an order is ever going to be relevent in later uses.

There is a conceptual question - is items_groups an entity or is only a table that signifies rapport between two organizations? Whether it's an entity then, even when you will find no additional characteristics, I'd advocate for any separate ID column for that entity. Whether it's rapport, if you will find additional characteristics (say, begin_date, finish_date or something like that like this), I'd advocate to possess a multi-column primary key.