RSS

How to design social network database

10 Feb
 

The Tables

Want to hear the best part about all of this? The core idea of myspace, and indeed all of the social networking sites, is the ability to connect to friends or colleagues and have them listed on your page. This can be accomplished with a total of two, yes, you heard it, two database tables. Figure shows an example of what these tables might look like. Please note that the Accounts table should have a lot more information

Querying The Data

In this section you will find common queries that you can use to pull data out of the database. For example, Listing 1 shows how you would get a list of your friends.

SELECT DISTINCT a.*
FROM Accounts a
INNER JOIN Friends f ON a.ID = f.friendID
WHERE f.AccountID = @ID

Listing 1: Selecting all of your friends

If you want to see everyone that has assigned you as a friend, use the query in Listing 2 .

SELECT DISTINCT a.*
FROM Accounts a
INNER JOIN Friends f ON a.ID = f.AccountID
WHERE f.FriendID = @ID

Listing 2: Selecting everyone that has you as a friend

And depending on your implementation, if you want to simply see everyone you are friends with (whether you added them, or they added you), you can use the UNION keyword to combine the results of both queries shown above (Listing 3).

SELECT DISTINCT a.*
FROM Accounts a
INNER JOIN Friends f ON a.ID = f.friendID
WHERE f.AccountID = @ID

UNION

SELECT DISTINCT a.*
FROM Accounts a
INNER JOIN Friends f ON a.ID = f.AccountID
WHERE f.FriendID = @ID

Listing 3: Query for everyone you are friends with (Mutually Inclusive).

Listing 4 will tell you if someone is the current user’s friend. The current user’s ID will be in the @ID parameter, whereas the person whom you are trying to determine is a friend or not will be in the @Friend parameter. When the count value is returned, if it is greater than zero, the person is a friend.

SELECT count(*)
FROM Friends f
WHERE
(f.AccountID = @ID AND f.FriendID = @Friend)
OR
(f.AccountID = @Friend AND f.FriendID = @ID)

Listing 4: Figure out if someone is your Friend.

 

Referenced by: http://dbdesign-sn.blogspot.com/2008/11/how-to-design-social-network-database.html

Advertisements
 
1 Comment

Posted by on February 10, 2011 in General

 

One response to “How to design social network database

  1. planters look

    January 14, 2015 at 2:42 AM

    I got this web page from my buddy who shared with me regarding this website and now this time I am browsing this web
    page and reading very informative articles at this place.

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: