MySQL Lookup table and id/keys

Question

Hoping someone can shed some light on this: Do lookup tables need their own ID?

For example, say I have:

Table users: user_id, username

Table categories: category_id, category_name

Table users_categories: user_id, category_id

Would each row in "users_categories" need an additional ID field? What would the primary key of said table be? Thanks.

Answer 1

You could create a composite key that uses the both keys

Normally if there is no suitable key to be found in a table you want to create a either a composite key, made up of 2 or more fields,

ex:

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)); 

therefor in your case you could add code that does the following:

ALTER TABLE users_categories ADD PRIMARY KEY (user_id, category_id);

therefor once you want to reference a certain field all you would need is to pass the two PKs from your other table, however to link them they need to each be coded as a foreign key.

ALTER TABLE users_categories ADD CONSTRAINT fk_1 FOREIGN KEY (category_id) REFERENCES categories (category_id);

but if you want to create a new primary key in your users_categories table that is an option. Just know that its not always neccessary.

Answer 2

Every table needs a primary key and unique ID in SQL no matter what. Just make it users_categories_id, you technically never have to use it but it has to be there.

Answer 3

You have a choice. The primary key can be either:

1. A new, otherwise meaningless INTEGER column.

2. A key made up of both user_id and category_id.

I prefer the first solution but I think you'll find a majority of programmers here prefer the second.

Answer 4

If your users_categories table has a unique primary key over (user_id, category_id), then - no, not necessarily.

Only if you

• want to refer to single rows of that table from someplace else easily
• have more than one equal user_id, category_id combination

you could benefit from a separate ID field.