We all like friends, and having the ability to add and remove friends within a social network is critical. How you deal with managing friends from a design point of view is an interesting one.

There are many approaches, depending on the type of information you want to store about the relationships that are created, whether you want to know who initiated the friendship, when it was created, when it was accepted (or rejected even), the type of friendship and so on.

For my little mobile social network, I’m going to use a simplified form of friend relationship tracking, as all I’m interested in is whether or not a relationship exists between two people. To do this, I’m going to use a simple keyless table that holds two values: userID and friendID.

What I am interested in here is whether or not there is a relationship within the table between two user IDs. This tells me whether or not two users are potential friends, or whether a friend request has been made. To do this, I simply enter a row in the database containing a person’s userID, and the userID of the person they wish to befriend. This sets up a one-way relationship in the friends table, indicating that a friendship request has been made. The actual mechanic of processing the friend request I’m going to pass off to the messaging subsystem (which I’ll be covering in another post).

Now that we have a single row in the database, a simple query against a userID will tell me how many ‘friends’ this person has. Of course, this will include all unaccepted friendships, which is not necessarily something we want.

However, this approach works both ways: once a friendship is accepted, a second row is entered in the database with the friend’s userID listed first, and the requestor’s userID listed second. This allows us to write very simple queries to return a complete list of a particular userID’s accepted friends, or a list of a particular userID’s friend requests, since our ‘key’ is the first column in our two column table.

And rejecting a friend request is as simple as removing the original row from the table.

This schema can obviously be expanded to include timestamping should this be needed, or friend type, or anything else really - they key concept here is that what’s of interest is whether or not there is a relationship in the database, not the content of the returned rows (i.e. rowcount = 0, 1 or 2 - this is the information I want).