MySQL friends table

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开发者_StackOverflow in the DB or is there a better way?

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.

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.

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

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.

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