MySQL friends table structure help?

Question

I was wondering what else should I add to my friends table how can i stop a user from adding the same friend twice as well as when the user is online? As well as any suggestions on how i can make my friends table better?

Here is my MySQL table.

CREATE TABLE friends (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id INT UNSIGNED NOT NULL,
friend_id INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
);

Answer 1

You don't need an id on a pivot table and could define your primary key like this PRIMARY KEY(user_id, friend_id), this would prevent from having duplicates.

Answer 2

IMO there's no need for id attribute.
You can add timestamp, which can be useful sometimes.
Create key for both user_id and friend_id, and they will be unique, which prevents you from creating this tuple twice.

Answer 3

well if the deal it's about the table design you could make the combination of the user_id and the friend_id as an unique key, or maybe to make all those three(3) fields as primary keys,,, not so good practice but works.

The other thing would be for you to controll this by using PHP or the connection language you alreade chose.

Let me know if this helps.