Database Schema - OByrnes/chatter-app GitHub Wiki

Users

column name	datatype	details
id	integer	primary key, not null
username	string	not null
email	string	not null, unique
hashed_password	string	not null
blob_id	integer	foreign key

• index on email, unique: true
• blob_id references Blobs table, nullable: true

Messages

column name	datatype	details
id	integer	primary key, not null
body	text	not null
user_id	integer	not null, foreign key
conversation_id	integer	not null, foreign key
created_at	datetime	not null
blob_id	integer	foreign key

• user_id references Users table
• conversation_id references Conversations table
• blob_id references Blobs table, nullable: true

Conversations

column name	datatype	details
id	integer	primary key, not null
name	string	not null

UsersConversations

column name	datatype	details
id	integer	primary key, not null
user_id	integer	not null, foreign key
conversation_id	integer	not null, foreign key

• user_id references Users table
• conversation_id references Conversations table

Notifications

column name	datatype	details
id	integer	primary key, not null
user_id	integer	not null, foreign key
notifiable_id	integer	not null, foreign key
notifiable_type	enum	not null

• user_id references Users table
• notifiable_id and notifiable_type combination references Messages, Blocks, or Friends table

Friends

column name	datatype	details
id	integer	primary key, not null
user_id	integer	not null, foreign key
friend_id	integer	not null, foreign key

• user_id references Users table
• friend_id references Users table

Blocks

column name	datatype	details
id	integer	primary key, not null
user_id	integer	not null, foreign key
enemy_id	integer	not null, foreign key

• user_id references Users table
• enemy_id references Users table

Blobs

column name	datatype	details
id	integer	primary key, not null
body	varbinary	not null
file_extension	string	not null