---
# System prepended metadata

title: Hardik

---

# Hardik
- Solved for Db design problem. lots of issue with table design and db queries. Please skip

# Question 1: DB design. 

- user service. 
- chat service. 
    - 1<>1 chat. 
    - text message
    - list of conversations, sorted, paginated.  Query 
    - list of messages, sorted, paginated.  Query

// timstamp
// user -> n number of conversations(user2, user3, user4) -> 20 conversta
// converstaion -> m messages -> 20 message

TableName -> ColumnsName -> DataType.

user_conversations (index -> user_id * last_updated)
-----------
user_id int
receiver_id int
conversation_id int
created_at timestamp
last_updated timestamp

conversation_message
-----------
converstaion_id int
user_id int
message TEXT
created_at timestamp



cid1 userA ...  0
cid1 userB ...  1

user A user B cid1 
user B user A cid1 


select converstaion_id from user_conversations where user_id = ? ordered by lastUpdated desc;
select user_id,message from conversation_msg where cid = ? ordered by createdat desc;



user1 -> user2 -> converstaionId createdOn lastUpdated
user1 -> user3 -> conversationId

cid1 -> message timestamp
cid1 -> message1 0
cid1 -> message2 1