# 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.