Schema

Database Schema

Users

Column Name	Data Type	Details
id	integer	not null, primary key
username	string	not null, indexed
email	string	not null, indexed, unique
password_digest	string	not null
session_token	string	not null, indexed, unique
avatar_url	string	not null
created_at	datetime	not null
updated_at	datetime	not null

Servers

Column Name	Data Type	Details
id	integer	not null, primary key
admin_id	integer	not null, indexed, foreign key
server_name	string	not null, indexed
avatar_url	string	not null
private	boolean	not null
created_at	datetime	not null
updated_at	datetime	not null

• admin_id references the users table
• private refers to the server being private or public indicated by a true or false value
• A public server functions as a normal server, while a private server is exclusively used for private chats between pre-defined users.

Members

Column Name	Data Type	Details
user_id	integer	not null, indexed, foreign key
server_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• This table functions as a joins table of all the users' servers
• user_id references the users table
• server_id references the servers table

Channels

Column Name	Data Type	Details
id	integer	not null, primary key
channel_name	string	not null, indexed
server_id	integer	not null, indexed, foreign key
channel_type	string	not null
created_at	datetime	not null
updated_at	datetime	not null

• server_id references the servers table
• channel_type refers to either voice or text

Messages

Column Name	Data Type	Details
id	integer	not null, primary key
body	string	not null, indexed
link	string	optional
author_id	integer	not null, indexed, foreign key
channel_id	integer	not null, indexed, foreign key
created_at	datetime	not null
updated_at	datetime	not null

• author_id references the users table
• channel_id references the channels table

Considerations

• Consider a polymorphic association and making channel_id into channelable
• How to implement private chat with existing structures? Or create new structures?