Database Documentation

Jump to bottom

Fxuls edited this page Jul 26, 2022 · 19 revisions

Database Schema Documentation

`statuses`

id INTEGER, PK
status VARCHAR(15) NOT NULL, UNIQUE

status values: default, busy, idle, offline

`images`

id INTEGER, PK
url VARCHAR(120), NOT NULL, UNIQUE

`users`

id INTEGER, PK
username VARCHAR(28), NOT NULL, UNIQUE (example: username#1234)
hashed_password VARCHAR(255), NOT NULL
bio VARCHAR(320)
status_id INTEGER, NOT NULL, FK statuses.id, DEFAULT statuses.id WHERE status = "default"
banner_color VARCHAR(10) (example: #C45D43)
profile_image_id INTEGER, FK images.id

`friend_requests`

id INTEGER, PK
sending_user_id INTEGER, NOT NULL, FK users.id
receiving_user_id INTEGER, NOT NULL, FK users.id

`friends`

id INTEGER, PK
user_one_id INTEGER, NOT NULL, FK users.id
user_two_id INTEGER, NOT NULL, FK users.id

`direct_message_chats`

id INTEGER, PK
user_one_id INTEGER, NOT NULL, FK users.id
user_two_id INTEGER, NOT NULL, FK users.id

`servers`

id INTEGER, PK
owner_id INTEGER, NOT NULL, FK users.id
name VARCHAR(40), NOT NULL
invite_url VARCHAR(120)
server_image_id INTEGER, FK images.id
public BOOLEAN, NOT NULL, DEFAULT TRUE

`server_permissions`

id INTEGER, PK
name VARCHAR(15), NOT NULL, UNIQUE
permission INTEGER, NOT NULL, UNIQUE

server_permission values: banned, member, moderator, admin

`server_members`

id INTEGER, PK
user_id INTEGER, NOT NULL, FK users.id
server_id INTEGER, NOT NULL, FK servers.id
permission_id INTEGER, NOT NULL, DEFAULT server_permissions.id WHERE server_permission = "member"

`channels`

id INTEGER, PK
server_id INTEGER, NOT NULL, FK servers.id
name VARCHAR(40), NOT NULL

`direct_messages`

id INTEGER, PK
direct_message_chat_id INTEGER, NOT NULL, FK direct_message_chats.id
text VARCHAR(800)
image_id INTEGER, FK images.id

`server_messages`

id INTEGER, PK
sender_id INTEGER, FK users.id
server_id INTEGER, NOT NULL, FK servers.id
channel_id INTEGER, NOT NULL, FK channels.id
text VARCHAR(800)
image_id INTEGER, FK, images.id