# Vasu Jain | ~5 years
- Very basic de design, struggles with writing queries.
- Please pass
Auth Service | 3P integrations

# Question 1: Chat db design

- User service.

- Chat service
- 1<>1 chat
- text message
- user:message = 1:10000
- list of conversations, sorted, paginated.
- list of message, paginated, sorted.

entities:
user
text_message
conversation

relations:

user can chat with another user

user can generate a text message

message is a part of a conversation

user -> multiple conversation
conversation -> multiple users

user -

user :
user_id , name , ph ,

message :
msg_id , text, user_id(fk), conversation_id(fk) , created_at

conversation :
conversation_id , name(op) , created_at, updated_at

user_conversations table:
user_id, conversation_id

user_conversations:

user_id: 1 | conversation_id : 1

user_id: 1 | conversation_id : 2

user_id

select * from user_conversations where user_id = 1;

select * from conversation where conversation_id in []

select c.conversation_id, c.name , c.created_at, c.updated_at FROM conversation c JOIN user_conversations uc ON c.conversation_id = uc.conversation_id WHERE user_id = 1 ORDER BY c.created_at

user 1 -> online :

user 2 open app -> user1 is online