CODEWITHSUNDEEP
sqldatabasedatabase-design
VansFannel
VansFannel

Reputation: 45991

How to know how many unread group messages I have

I'm designing a database. This is what I have to represent:

To manage conversations between users, and group I have a table (Talk) with these columns:

TalkId           (NOT NULL, PK)
Type             (NOT NULL, values: UserTalk or GroupTalk)
StarterUserId    (NOT NULL, the user that has started the talk). 
RecepientUserId: (NULL, the user that has received the first message. NULL if it is a GroupTalk).
DateStarted:     (NOT NULL, when the talk has been started).
GroupId:         (NULL, the group that owns the talk. NULL if it is a UserTalk)

I also have a Message table to store all the message for each Talk. This Message table has a column Read to indicate that the recipient has read or not the message.

If user 1 sends a message to a user 2, first I check if there is a Talk row with:

((StarterUserI == 1 and RecepientUserId == 2) OR
 (StarterUserI == 2 and RecepientUserId == 1))

If there isn't, I create a new row on it. Then, I insert the message in Message table with Message.TalkId pointing to the row that I have created.

My problem is that I don't know how to know how many unread message a user has for a group talk.

For a user talk is easy checking if Message.Read column is false.

To know if a user has unread messages on a group's talk, I can insert the same message for each group member, changing the recipient. For example:

I have a group, with three members. Member 1 send a message to a group. I have to insert a message to user 2, and the same message to user 3:

enter image description here

But, this can make grow Message table very fast.

I've thought to add new two columns to Talk table, the date for the last message sent to that talk, and the id of user that has sent that last message. If I have the date and the ID for the last message in a talk, I can check if there are new messages, but I can't know how many.

I have also a UserGroup table to store the users that are members of a group, and the users' groups. I can add a new column to this table to store how many messages a user has for a group talk. Every time another user send a message to that group, I'm going to insert a new row on Message table, and increase the value on UserGroup.Unread by one. But I think I'm going to mess the design.

How can I know how many unread message a user has for a Group Talk?

Upvotes: 2

Views: 1013

Answers (1)

Twinkles
Twinkles

Reputation: 1994

You can add a new table MessageStatus with the columns UserID, MessageID and Read where you add one row for each recipient of a message (UserTalk or GroupTalk). This avoids the redundancies you would introduce when duplicating rows in the Message table.

For convenience you could introduce an INSERT-trigger on Message to create the rows in MessageStatus.

Upvotes: 3

Related Questions