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)
);
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.
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.
No, catID isn't unique because there are two fours.
I don't think you want catID to be unique, as you have it up there multiple times.
