Wishing someone can shed some light about this: Do research tables need their very own ID?

For instance, say I've:

Table users: user_id, username

Table categories: category_id, category_name

Table users_categories: user_id, category_id

Would each row in "customers_groups" need one more ID area? An amount the main key of stated table be? Thanks.

You've got a choice. The main key could be either:

  1. A brand new, otherwise meaningless INTEGER column.

  2. A vital comprised of both user_id and category_id.

I favor the very first solution but I think you will find most developers here like the second.

You can produce a composite key that utilizes the two secrets

Normally if there's no appropriate key found inside a table you need to produce a whether composite key, comprised of two or more fields,


Code below found here

  CREATE TABLE topic_replies (
topic_id int unsigned not null,
id int unsigned not null auto_increment,
user_id int unsigned not null,
message text not null,
PRIMARY KEY(topic_id, id)); 

because of this inside your situation you could include code that does the next:

ALTER TABLE customers_groups ADD PRIMARY KEY (user_id, category_id)

because of this once you need to reference a particular area whatever you would want would be to pass the 2 PKs out of your other table, however to link them they have to each be coded like a foreign key.

ALTER TABLE customers_groups ADD CONSTRAINT fk_1 FOREIGN KEY (category_id) REFERENCES groups (category_id)

but when you need to produce a new primary type in your customers_groups table that's a choice. Just realize that it is not always neccessary.

Every table requires a primary key and different ID in SQL regardless of what. Simply make it customers_groups_id, you technically never need to utilize it however it needs to be there.

In case your users_categories table includes a unique primary key over (user_id, category_id), then - no, not always.

Only when you

  • want to consult single rows of this table from somewhere else easily
  • have several equal user_id, category_id combination

you may need another ID area.