MySQL table structure question.

Question

I have a table that holds a users favorite categories and I was wondering if My MYSQL tables structure is built correctly to hold the MySQL data provided correctly?

MySQL data.

userID      catID
4       21
4       4
4       67
3       34
3       4

MySQL table .

CREATE TABLE ab (
userID INT UNSIGNED NOT NULL, 
catID INT UNSIGNED NOT NULL,
PRIMARY KEY (userID),
UNIQUE KEY (catID)
);

Answer 1

No, catID isn't unique because there are two fours.

Answer 2

I don't think you want catID to be unique, as you have it up there multiple times.

Answer 3

No it is not correct: userId is not unique, nor is catID, the couple(userID, cat ID) is. Either you add a third column to act as primary key, and you declare the couple (userId, catID) as unique, or you can even declare this very couple as primary key.

Answer 4

Neither userID nor catID are by themselves unique. What you want is

CREATE TABLE ab (
    userID INT UNSIGNED NOT NULL, 
    catID INT UNSIGNED NOT NULL,
    PRIMARY KEY (userID, catID)
);

so that only the specific combinations of userID and catID taken together are required to be unique.