SQL Server Helper - Tip of the Day

If the database of Twitter was implemented using SQL Server, particularly the Twitter Tweets, how would the database structure look like?  Here's an attempt of designing a database structure that can be used to store and quickly retrieve short user messages called Tweets similar to the ones used by Twitter.  This article is a continuation of yet another article regarding SQL Server Database Design for Twitter Profile and Followers.

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

As a background, the article SQL Server Database Design for Twitter Profile and Followers provided two tables that will store the Twitter accounts and the relationship between these Twitter accounts called Followers, as shown below:

CREATE TABLE [dbo].[Account](
    [AccountID]        INT IDENTITY(1,1) NOT NULL,
    [Email]            VARCHAR(100) NOT NULL,
    [FullName]         NVARCHAR(100) NOT NULL,
    [Password]         VARBINARY(100) NOT NULL,
    [UserName]         VARCHAR(15) NOT NULL,
    [FollowerCount]    INT NOT NULL DEFAULT ( 0 ),
    [FollowingCount]   INT NOT NULL DEFAULT ( 0 ),
    [TweetCount]       INT NOT NULL DEFAULT ( 0 ), 
    CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED ( [AccountID] ASC )
)
GO

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

To continue the database design of Twitter, we now look into how the Twitter Tweets may be stored in SQL Server.  A Twitter Tweet is simply a short message by a Twitter user of up to 140 characters.  These Tweets then get displayed in the Timeline of the user as well as in the Timeline of the followers of the user.  Twitter Tweets can easily stored in a simple table which we will appropriately call [dbo].[Tweet]:

CREATE TABLE [dbo].[Tweet](
    [TweetID]        INT IDENTITY(1,1) NOT NULL,
    [AccountID]      INT NOT NULL REFERENCES [dbo].[Account] ( [AccountID] ),
    [Message]        NVARCHAR(140) NOT NULL,
    [TweetDate]      DATETIME NOT NULL DEFAULT ( GETUTCDATE() ),
    CONSTRAINT [PK_Tweet] PRIMARY KEY CLUSTERED ( [TweetID] ASC )
)
GO

To retrieve the Tweets of a particular user, the following query can be used:

SELECT [Message], [TweetDate]
FROM [dbo].[Tweet]
WHERE [AccountID] = @AccountID
ORDER BY [TweetDate] DESC

To retrieve the Tweets of all users that a particular user is following, the following query can be used:

SELECT B.[FullName], B.[UserName], C.[Message], C.[TweetDate]
FROM [dbo].[Follower] A INNER JOIN [dbo].[Account] B
                                ON A.[AccountID1] = @AccountID AND
                                   A.[AccountID2] = B.[AccountID]
                        INNER JOIN [dbo].[Tweet] C
                                ON A.[AccountID2] = C.[AccountID]
ORDER BY C.[TweetDate] DESC, B.[FullName] ASC

To make these queries execute fast, an index on the [AccountID] column needs to be created:

CREATE NONCLUSTERED INDEX [IX_Tweet] ON [dbo].[Tweet] (
    [AccountID] ASC,
    [TweetDate] DESC
)
GO

In its simplest form, Twitter can be maintained using just three tables, namely [dbo].[Account], [dbo].[Follower] and [dbo].[Tweet] tables.