Database design

[CUexter]

The designed schema for the Twitter Clone is composed of several tables:

1. users table stores information about each user including their username, password, email, display name, tag name, last login time, and a flag to indicate whether they are an admin user.
2. tweets table stores information about each tweet including the tweet ID, user ID, text, created time, published time, scheduled time, and a flag to indicate whether the tweet is public or private.
3. tweet_text table stores the text of each tweet and keeps track of its history if it was edited. It has a one-to-many relationship with the tweets table.
4. tweet_hashtags table stores the hashtags associated with each tweet and has a many-to-many relationship with the hashtags table.
5. tweet_attachments table stores information about each attachment for a tweet, including the attachment ID, tweet ID, type, and URL.
6. hashtags table stores all hashtags used in the Twitter Clone.
7. tweet_polls table stores information about each poll associated with a tweet, including the poll ID, tweet ID, end time, and a one-to-many relationship with the poll_options table.
8. poll_options table stores the options for each poll and the number of votes for each option.
9. retweets table stores information about each retweet, including the retweet ID, user ID, tweet ID, and the time it was retweeted. It has a one-to-one relationship with the tweets table.
10. followers table stores the relationships between users and has two fields, follower_id and followed_id, which create a many-to-many relationship between users.
11. likes table stores the relationships between users and tweets, indicating which user liked which tweet. It has two fields, user_id and tweet_id, creating a many-to-many relationship between users and tweets.

All tables will have id, created, updated by default

[CUexter]

users table

Column	Data Type	Key	Description
id	INT	PRIMARY KEY	A unique identifier for each user
username	VARCHAR(255)	NOT NULL, UNIQUE	The username of the user
password	VARCHAR(255)	NOT NULL	The password of the user
email	VARCHAR(255)	NOT NULL, UNIQUE	The email of the user
email_visibility	BOOLEAN		Whether the user's email to the public
verified	BOOLEAN		Whether the user's email is verified
display_name	VARCHAR(255)	NOT NULL	The display name of the user
tag_name	VARCHAR(255)	NOT NULL, UNIQUE	The tag name of the user
profile_description	TEXT		A brief description of the user's profile
profile_picture	VARCHAR(255)		The URL of the user's profile picture
avatar	VARCHAR(255)		The URL of the user's avatar
is_admin	BOOLEAN	NOT NULL	A flag to indicate if the user is an admin or not
last_login_time	DATETIME		The date and time of the user's last login

[FAL0258]

In order to implement user Ban feature (banned by admin), maybe we should add one more attribute to indicate the account status? (e.g. is_ban)

[CUexter]

tweets table

Column	Data Type	Key	Description
user_id	INT	NOT NULL	The user_id of the user who posted the tweet
is_public	BOOLEAN	NOT NULL	Indicates whether the tweet is public or private
tweet_text_id	INT	NOT NULL, UNIQUE	The id of the latest tweet text that is associated with this tweet
scheduled_at	DATETIME		The date and time the tweet is scheduled to be posted
published_at	DATETIME		The date and time the tweet was published

[CUexter]

if published_at is not set, then it is a draft

[CUexter]

tweet_text table

Column	Data Type	Key	Description
tweet_id	INT	NOT NULL	The tweet_id of the tweet that the text belongs to
tweet_text	VARCHAR(255)	NOT NULL	The text of the tweet

[CUexter]

This way, each tweet can have multiple versions of its text, with the history of text changes stored in the tweet_text table. The relationship between the two tables is a one-to-many relationship, where one tweet can have multiple texts but each text only belongs to one tweet.

[CUexter]

hashtags table

Column	Data Type	Key	Description
hashtag_text	VARCHAR(255)	NOT NULL, UNIQUE	The text of the hashtag

[CUexter]

tweets_hashtags table

Column	Data Type	Key	Description
tweet_id	INT	NOT NULL	The tweet_id of the tweet associated with the hashtag
hashtag_id	INT	NOT NULL	The hashtag_id of the hashtag associated with the tweet

[CUexter]

This way, the tweets_hashtags table acts as a join table between the tweets and hashtags tables, allowing for the many-to-many relationship between tweets and hashtags to be established.

[CUexter]

following table

Column	Data Type	Key	Description
user_id	INT	NOT NULL	The user_id of the person being followed
follower_id	INT	NOT NULL	The user_id of the person doing the following

[CUexter]

likes table

Column	Data Type	Key	Description
user_id	INT	NOT NULL	The user_id of the person who liked the tweet
tweet_id	INT	NOT NULL	The tweet_id of the tweet that was liked

[CUexter]

retweets table

Column	Data Type	Key	Description
retweet	INT	NOT NULL	The tweet that is the retweet
original_tweet	INT	NOT NULL	The tweet_id of the tweet that was retweeted
on_profile	Boolean	NOT NULL	True for retweets, false for reply

[CUexter]

The retweets table would have a many-to-one relationship with the tweets table (many retweets can belong to one tweet).

[CUexter]

polls table

Column	Data Type	Key	Description
tweet_id	INT	NOT NULL UNIQUE	The tweet_id of the tweet that this poll is associated with
end_time	DATETIME		The date and time the poll will end (optional)

[CUexter]

unique if each tweet can only have one poll

[CUexter]

poll_options table

Column	Data Type	Key	Description
poll_id	INT	NOT NULL	The poll_id of the poll that this option is associated with
option_text	TEXT	NOT NULL	The text of the option

[CUexter]

I think we need a voting table

voting table

Column	Data Type	Key	Description
user_id	INT	NOT NULL	The user_id of the voter
poll_options_id	INT	NOT NULL	The poll_options_id the voter chose

[CUexter]

tweet_attachments table

Column	Data Type	Key	Description
tweet_id	INT	NOT NULL	The tweet_id of the tweet that this attachment is associated with
attachment_type	ENUM('image', 'video')	NOT NULL	The type of attachment (image or video)
attachment_url	TEXT	NOT NULL	The URL of the attachment

[CUexter]

The chat functionality of the database is comprised of the following tables:

1. conversations: This table contains information about direct messages between two users. It has fields to store the unique IDs of the sender and receiver, as well as the ID of the message content.

2. message_content: This table stores the content of the messages, be it text, image, video, or voice. It has fields for the unique identifier of the message, the content itself, and the type of content (e.g. text, image, video, voice).

3. group_chats: This table contains information about the group chats in the system, including the unique ID of the chat and its name.

4. group_chat_members: This table establishes the many-to-many relationship between group chats and users, by linking the unique IDs of both.

[CUexter]

conversations table

Column Name	Data Type	Constraint	Description
sender_id	INT	FOREIGN KEY	User who sent the message
chatroom_id	INT	FOREIGN KEY	Chatroom the message is sent to
content_id	INT	FOREIGN KEY	Reference to the message content table

[CUexter]

message_content table

Column Name	Data Type	Constraint	Description
content	TEXT	NOT NULL	Content of the message, could be a URL pointing to a file or just plain text
content_type	VARCHAR(255)	NOT NULL	Denotes how the content should be treated (e.g. text, image, video, voice)

[CUexter]

chatroom table

Column Name	Data Type	Constraint	Description
name	VARCHAR(255)	NOT NULL	Name of the chatroom

[CUexter]

chatroom_members table

Column Name	Data Type	Constraint	Description
user_id	INT	FOREIGN KEY	User who is a member of the group chat
chatroom_id	INT	FOREIGN KEY	chatroom the user is a member of