MySQL friends table

Question

I have a MySQL DB in which I store data about each user.

I would like to add a list of friends for each user. Should I create a table of friends for each user in the DB or is there a better way?

Are friendships bidirectional or unidirectional? If Pete is friend of Mary, does it always mean that Mary is also friend of Pete? — Juha Syrjälä, Jun 09 '10 at 19:38
Also note that the name of what you would like is "intersection table" or maybe also "lookup table". If you're familiar with database concepts, it's a many to many table, as a user may have many friends, and may be many friends with other people. Also note that it's possible for a user to have a friend who is not a friend back, using this style. There are other ways as well, but this is the simplest to implement. Maybe a bit more about your friending business logic would help? @Juha Syrjälä beat me to it! — jcolebrand, Jun 09 '10 at 19:38
Friends are unidirectional and must already exist in the DB. It's just a reminder list of people you know. — , Jun 09 '10 at 19:53
Also note the comments in my answer about indexes - there's nothing complex required but you will need to add the correct ones to keep access fast on larger volumes (you'll end up with time-consuming full table scans elsewise) - so don't overlook them. — Cruachan, Jun 09 '10 at 19:54

Cruachan · Accepted Answer · 2010-06-09T19:50:02.530

Assuming all your friends are also in the user table you will need a friends table which defines a simple one-to-many relationship - linking the users table back to itself. So

User Table
UserID int identity not null
[other attribute fields]

Friends Table
UserIDLink1 int
UserIDLink2 int 
[other attribute field]

Where both UserIDLink1 and UserIDLink2 are foreign keys on the Users table.

So for instance if I have three users

1 Joe
2 Bill
3 Jane

and Joe and Jane are friends then the Friends table would contain a single row

1 3

The above implicitly assumes that if A is a friend of B then B is a friend of A - if this isn't the case you'd probably want to rename UserIDLink1 and UserIDLink2 to UserID and FriendID or similar - in which case you'd have up to double the records too.

Also for the bi-directional configuration (A is a friend of B if B is a friend of A) you should set up indexes on the Friends table for (UserIDLink1,UserIDLink2) and (UserIDLink2,UserIDLink1) to ensure access is always efficient if we were searching either for friends of joe or friends of jane (if you didn't set up the second index then the first query would be an efficient index lookup but the second would require a full table scan).

If your links were not bidirectional this wouldn't be necessary to find out who A's friends are, but you would still probably most require it as you'll likely also need to find out who B is a friend of.

I wonder, how you formulate join query with two foreign key (userIDLink1 and userIDLink2) pointing at same primary key? — koceeng, Jan 01 '17 at 12:42

score 6 · Answer 2 · answered Jun 09 '10 at 19:38

6

Assuming your USER table has a primary key called id or something similar, use the following table:

DROP TABLE IF EXISTS `friends`;
CREATE TABLE `friends` (
  `user_id` int(10) unsigned NOT NULL,
  `friend_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`user_id`,`friend_id`),
  KEY `FK_FRIENDS_2` (`friend_id`),
  CONSTRAINT `FK_FRIENDS_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `FK_FRIENDS_2` FOREIGN KEY (`friend_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

This setup supports that Peter is a friend of Mary, but Mary doesn't think of Peter like that. But the data exists to infer that Peter is an acquaintance for Mary...

The primary key being both columns also stops duplicates.

answered Jun 09 '10 at 19:38

OMG Ponies

325,700
82
523
502

Am I missing the assignment of `FK_FRIENDS_1` there? – jcolebrand Jun 09 '10 at 19:40
1

More clean with user_id and friend_id – rigobcastro Oct 19 '15 at 23:51
if user1 is friend of user3 and u will insert record 1,3 and u want to check friends of 3 then can you write the query? – Sunil Garg Oct 28 '20 at 10:07

score -1 · Answer 3 · answered Jun 09 '10 at 19:31

-1

Create a table that contains all friends Each row in the table will contain the ID of the user and the id of their friend

answered Jun 09 '10 at 19:31

Josh Mein

28,107
15
76
87

So for each friend of each user, I add a row in a 2-column table (User, Friend). For instance, if I have 200 users with 10 friends each, the "Friends" table should have 2000 rows (10 per user). Do I understand correctly? – Jun 09 '10 at 19:42
1

@asmo that's correct, but with a highly index-optimised table such as you can have in this case you shouldn't be afraid of tables with in excess of a million rows - that won't be a problem. – Cruachan Jun 09 '10 at 19:52

score -1 · Answer 4 · answered Jun 09 '10 at 19:41

-1

You are looking for M-to-N or many-to-many join table.

Table Users:

USER_ID  integer primary key,
NAME     varchar

Table Friendships

USER_ID    integer not null,
FRIEND_ID  integer not null,

Both USER_ID and FRIEND_ID are foreign keys that reference Users table (Users.user_id).

If user 123 is friend of user 921. Add row (123, 921) to Friendships table.

answered Jun 09 '10 at 19:41

Juha Syrjälä

33,425
31
131
183

You're only showing 2 tables? Isn't a one-to-many enough for this? – Miguel Stevens Apr 17 '15 at 07:19
1

and since user 921 is friend of 123 then how you will query that? – Sunil Garg Oct 28 '20 at 10:21

score -2 · Answer 5 · answered Jun 09 '10 at 19:30

-2

Create a single table for all the friends and give each friend a UsersID which is equal to their respective users key

answered Jun 09 '10 at 19:30

geshafer

299
2
10

MySQL friends table

5 Answers5

Linked