SQL Server Helper - Tip of the Day

If the database of Facebook was implemented using SQL Server, how would the database structure look like?  Here's an attempt of designing a database structure that can be used to store profile information and connections (also known as Friends) similar to the ones used by Facebook.

Disclaimer: The author of this article does not work for Facebook and has not worked for Facebook.  It is just the author's attempt on designing the database that may work on such site as Facebook.  Any similarities to the database design implemented by Facebook are mere coincidences (and luck).

The basic building block or the basic element in Facebook is an account. When a user signs up, the user is asked for the following information:

• First Name
• Last Name
• Email Address
• Password
• Birthdate
• Gender

Given these information, we can now create the first table in our database design, which we will call the Account table:

CREATE TABLE [dbo].[Account](
    [AccountID]  INT IDENTITY(1,1) NOT NULL,
    [Email]      VARCHAR(100) NOT NULL,
    [FirstName]  VARCHAR(50) NOT NULL,
    [LastName]   VARCHAR(50) NOT NULL,
    [Password]   VARBINARY(100) NOT NULL,
    [Birthdate]  DATE NOT NULL,
    [Gender]     CHAR(1) NOT NULL,
    CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED ( [AccountID] ASC )
)
GO

Other user information such as relationship status, website, anniversary, religion and political views, can be added to this table later but the basic requirements for now are just these columns.

Once an account has been created, the next thing a user usually does is to add new friends by doing a friend request.  The friend request event can be stored in a table, which we will appropriately call FriendRequest.

CREATE TABLE [dbo].[FriendRequest](
    [FriendRequestID]     INT IDENTITY(1,1) NOT NULL,
    [AccountID1]          INT NOT NULL REFERENCES [dbo].[Account] ( [AccountID] ),
    [AccountID2]          INT NOT NULL REFERENCES [dbo].[Account] ( [AccountID] ),
    [RequestDate]         DATETIME NOT NULL,
    CONSTRAINT [PK_FriendRequest] PRIMARY KEY CLUSTERED ( [FriendRequestID] ASC )
)
GO

If a friend request has been declined, that record will be removed from this table.  However, if the friend request has been accepted then that friend can now be associated with the user.   This account association will be maintained in another table, appropriately called the Friend table.

CREATE TABLE [dbo].[Friend](
    [FriendID]   [int] IDENTITY(1,1) NOT NULL,
    [AccountID1] [int] NOT NULL REFERENCES [dbo].[Account] ( [AccountID] ),
    [AccountID2] [int] NOT NULL REFERENCES [dbo].[Account] ( [AccountID] ),
    PRIMARY KEY CLUSTERED ( [FriendID] ASC )
)
GO

The friend request record will be deleted from the [dbo].[FriendRequest] table and two records will be created in the [dbo].[Friend] table. The first record will be the association between the user (as AccountID1) and the friend (as AccountID2) and the second record will be the opposite which is the relationship between the friend (as AccountID1) and the user (as AccountID2).

Although the [dbo].[FriendRequest] table can be merged together with the [dbo].[Friend] table and just add another column to signify the acceptance of the request, a separate table is used instead to separate the friends from friend requests to minimize the number of records being stored and queried when getting the list of friends for a particular user and thus making the query faster.

To get the list of friends for a particular user account, the following query can be used:

SELECT B.[Email], B.[FirstName], B.[LastName]
FROM [dbo].[Friend] A INNER JOIN [dbo].[Account] B
                              ON A.[AccountID1] = @AccountID AND
                                 A.[AccountID2] = B.[AccountID]

To get a list of common friends between two user accounts, the following query can be used:

SELECT C.[Email], C.[FirstName], C.[LastName]
FROM [dbo].[Friend] A INNER JOIN [dbo].[Friend] B
                              ON A.[AccountID1] = @AccountID1 AND 
                                 B.[AccountID1] = @AccountID2 AND
                                 A.[AccountID2] = B.[AccountID2]
                      INNER JOIN [dbo].[Account] C
                              ON A.[AccountID2] = C.[AccountID]

To make these queries execute faster, an indexes can be created on the [AccountID1] and [AccountID2] columns:

CREATE UNIQUE NONCLUSTERED INDEX [IX_Friend1] ON [dbo].[Friend] (
	[AccountID1] ASC,
	[AccountID2] ASC
)
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_Friend2] ON [dbo].[Friend] (
	[AccountID2] ASC,
	[AccountID1] ASC
)
GO