MySQL 'Friends' table

0
votes

I am trying to create a table for 'Friends' in MySQL.

I have a 'Users' table with an attribute of 'user_id' which is the table's primary key.

I would like a friendship table which allows a user to add another user as a friend. However that user will remain an acquaintance to the new friend.

So for example, USER_A connects with USER_B and USER_B is now a friend of USER_A, however USER_B does not see USER_A as a friend and thus USER_A is still only an acquaintance of USER_B.

1
mysqldatabase-design
Just add 2 rows: userA | userB and userB | userA - zerkms
What have you come up with so far? - reto
So far I have thought of having user_id, friend_id and date_started as a timestamp. - Pixulated

1 Answers

2
votes

You can model your table 'Friends' with 2 columns like this (pseudo code):

Friends(from, to)
PrimaryKey(from, to)

In real mysql:

CREATE TABLE Friends (from INT,
                      to INT,
                      PRIMARY KEY(from, to))

Assuming that you have an Users table with an integer as primary key.

Then, if you insert from = USER_A and to = USER_B, your intended semantic is already respected. Because you can also add another row having from = USER_B and to = USER_A.