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

You should use the ID's of the two users as PRIMARY KEY in a relationship table (the relation would be unique even if not bi-directional). Something like that

CREATE TABLE Users ( id int(9) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id) );

CREATE TABLE Relationships ( id1 int(9) NOT NULL, id2 int(9) NOT NULL, friended TIMESTAMP NOT NULL, PRIMARY KEY (id1, id2) );

Please note:

• id1 and id2 reference Users table (the code above was very simplified)
• even if the relationship is not "bi-directional" you can think that if you have id1 - id2 it seems that id1 user add id2 user as a friend but not necessarily the opposite - THEN id2 user can add id1 user as friend - in the table of relationships you can have these possible combinations:
  • id1-id2, (only 1 add 2 as a friend)
  • id2-id1, (only2 add 1 as a friend)
  • id1-id2 AND id2-id1 (both - it means 2 lines in relationships table, and both are MUTUAL friends)

Answer 2

A single linking table suffices, like so:

People( PersonId bigint, Name nvarchar, etc )
Friends( FromPersonId bigint, ToPersonId bigint, DateAdded datetime )

Example queries:

Who is friends with me? (i.e. people who have added me as a friend, but not necessarily reciprocated)

SELECT
    People.Name
FROM
    Friends
    INNER JOIN People ON Friends.FromPersonId = People.PersonId
WHERE
    Friends.ToPersonId = @myPersonId

Who added me between two dates?

SELECT
    People.Name
FROM
    Friends
    INNER JOIN People ON Friends.FromPersonId = People.PersonId
WHERE
    Friends.ToPersonId = @myPersonId
    AND
    Friends.DateAdded >= @startDate
    AND
    Friends.DateAdded <= @endDate

Answer 3

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.

Answer 4

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.