66 lines (46 loc) · 1.98 KB

Database

created four tables for convenient data storage

users
rooms
messages
members

users

a table that stores data about all users

id -> unique user number created from the primary key
name -> not a unique username
login -> unique user login for login
pass -> user password
token -> token for file transfer
date -> user registration time in milliseconds
desc -> user description

rooms

a table that stores data about all users

id -> unique room number created from the primary key
name -> not a unique room name
customer_id -> id of the user who created this room
date -> time to create a commandant in milliseconds
desc -> room description
type -> room type, example: DB_PRIVAT_CHAT, DB_GLOBAL_CHAT, DB_LS_CHAT

messages

a table that stores data about all messages

user_id -> the number of the user who created this message
room_id -> the number of the room to which the message was sent
message_id -> unique message number created from the primary key
date -> message creation time in milliseconds
message -> if the message has a message then it is its text else NULL
type -> type message, example: DB_TEXT_MSG, DB_FILE_MSG, DB_FILE_TEXT_MSG, DB_STICKER
file_size -> if the message has a file then it is its size else 0
file_name -> if the message has a file then it is its name else NULL
status -> status message, example: DB_MESSAGE_START, DB_MESSAGE_EDIT

members

a table that stores the location data of all users

user_id -> the number of the user who is in the room under the number room_id
room_id -> the room number in which the user is located under the user_id number
date -> time in milliseconds when the user entered user_id entered the room room_id
permission -> user permissions in room room_id