Twitter/X Tweets

SQL Server Database Design - X (Previously Known as Twitter) Tweets

If the database of X/Twitter was implemented using SQL Server, particularly the X/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 X/Twitter and has not worked for X/Twitter. It is just the author's attempt on designing the database that may work on such site as X/Twitter. Any similarities to the database design implemented by X/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 X/Twitter accounts and the relationship between these X/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 X/Twitter, we now look into how the X/Twitter Tweets may be stored in SQL Server. A X/Twitter Tweet is simply a short message by an X/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. X/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, X/Twitter can be maintained using just three tables, namely [dbo].[Account], [dbo].[Follower] and [dbo].[Tweet] tables.