# 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