---
# System prepended metadata

title: Dhawan Gupta

---

# Dhawan Gupta

# Question 1: DB design. Slack


- DMs chats (listing of DMs)
- channel (listing of channel)
- text message (listing of meesages)
- Workspace.



User - userId (PK)

Workspace - wsId(PK), userId(FK), name, orgLabel

Channel - channelId(PK), wsId(FK), label

UserChannelRelation - ucrId(PK), userId(FK), channelId(FK)

UserRelation - urId, user1Id(FK), user2Id(FK), lastMessageTime(DateTime)

DMChats - 
chatId(PK), SenderId(FK-userId), ReceiverId(FK-userId), timeOfChat(DateTime), message(TEXT), wsId(FK), urId(FK)

ChannelChats - 
chatId(PK), channelId(FK), SenderId(FK-userId), timeOfChat(DateTime), message(TEXT)



- DMs chats (listing of DMs)
Input - uId

SELECT user1Id s from DMChats where user2Id=uId ORDER BY lastMessageTime;
SELECT user2Id r from DMChats where user1Id=uId;

output of these queries can be stored in a set and can be shown



- channel (listing of channel)
Input - uId, wsId

SELECT channelId cId from Channel c where channelId = (Select channelId from UserChannelRelation where userId = uId) where wsId = wsId 



- text message (listing of meesages)
- Workspace.

