How to make SQL many-to-many same-type relationship table

Question

I'm a newbie to SQL and I'm jumping in head first trying to learn as much as possible as I'm coding, which is difficult as I'm designing the database I'll have to live with for a while so I want to make sure I do it right. I learned the basics of the Many-to-many bridge tables, but what if the two fields are the same type? Let's say a social network with thousands of users, and how would you create a table to keep track of who is friends with who? What if there is additional data about each relationship, say... "date friended" for example. Knowing that there will be queries like "show all friends of userX friended between dateY and dateZ". My database would have several situations like this and I can't figure out an efficient way to do it. Since it's coming up a lot for me, I figure others have figured out the best way to design the tables, right?

Answer 1

Create a User table then a Relationships table where you store the id of the two friend and any kind of information about their relationship.

SQL diagram

MySQL code

CREATE TABLE `Users` (   `id` TINYINT NOT NULL AUTO_INCREMENT DEFAULT NULL,   PRIMARY KEY (`id`) );  CREATE TABLE `Relationships` (   `id` TINYINT NOT NULL AUTO_INCREMENT DEFAULT NULL,   `userid` TINYINT NULL DEFAULT NULL,   `friendid` TINYINT NULL DEFAULT NULL,   `friended` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,   PRIMARY KEY (`id`) );  ALTER TABLE `Relationships` ADD FOREIGN KEY (userid) REFERENCES `Users` (`id`); ALTER TABLE `Relationships` ADD FOREIGN KEY (friendid) REFERENCES `Users` (`id`); 

SQL selection

After you fill up the tables with data, you can create your SQL SELECT query to get all of your friends. Your friends are those whose id is in one side side while your id is in the other side. You check both sides for your id so you don't need to store relationships twice. Also you have to exclude your id, because you can't be your own friend (in a normal, healthy world).

SELECT * FROM Users u    INNER JOIN Relationships r ON u.id = r.userid    INNER JOIN Relationships r ON u.id = r.friendid WHERE    (r.userid = $myid OR r.friendid = $myid)    AND r.friended >= $startdate    AND r.friended <= $enddate    AND u.id != $myid; 

Where $myid, $startdate and $enddate can be PHP variables so in double quotes you can pass this code directly to your database.

Answer 2

The Model

A model stretching over three tables would be an option. You'd have the obvious table user with all the user's specifics (Name, Date of Birth, ...).

CREATE TABLE `user` (   `id` INT NOT NULL AUTO_INCREMENT,   `name` VARCHAR(45),   `dob` DATE,   PRIMARY KEY (`id`) ); 

Second, a table connection of the user could contain privileges granted to a connection (can this connection see my photo album), and importantly, would refer to a table friendship. We need the table connection in between because one user can be connected into many friendships.

CREATE TABLE `connection` (   `id` INT NOT NULL AUTO_INCREMENT,   `user_id` INT NOT NULL,   `friendship_id` INT NOT NULL,   `privilege_mask` TINYINT,   PRIMARY KEY (`id`) ); 

friendship in turn could include shared details like when this friendship was established. Users that are connected to the same friendship are friends.

CREATE TABLE `friendship` (   `id` INT NOT NULL AUTO_INCREMENT,   `met_first_time` DATE,   PRIMARY KEY (`id`) ); 

This way would be a more realistic model than the other solutions posted so far in that it avoids directionality (unreciprocated friendship - which shouldn't exist!), but it would be a little more work to implement.

Query Friends

An example that should query the names of your friends, may be (although not tested):

SELECT B.name FROM user A   INNER JOIN connection conn_A ON conn_A.user_id = A.id     INNER JOIN connection conn_B ON conn_A.friendship_id = conn_B.friendship_id     INNER JOIN user B ON conn_B.user_id = B.id     INNER JOIN friendship ON friendship.id = conn_A.friendship_id   WHERE A.name = 'Dan' AND A.id <> B.id AND     friendship.met_first_time BETWEEN '2013-4-1' AND '2013-6-30'; 

You may notice that if you don't care about the date when you made friends, you don't need to JOIN to the friendship table because connections already share friendship_id keys. The essence of any such query would be the JOIN between conn_A and conn_B on conn_A.friendship_id = conn_B.friendship_id.