---
# System prepended metadata

title: Vasu Jain

---

# 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 