I want to keep track of unread messages for private chats for each user. I thought of two ways for doing this, both are pretty similar, the difference is the approach of select and partition key. So one user can have from 1 to 10.000+ contacts. When he's logging in he requests the count of unread messages for all his contacts.
First approach:
create table private_unread_counter (
from_id text,
to_id text,
count counter,
primary key ((from_id, to_id))
);
This approach means that I'll have to make a select for each contact that an user has. Something like that:
foreach (contact in requestingUserContacts)
{
unreadMsgsFromContact = select count from private_unread_counter where from_id=contact and to_id=requestingUser
}
Second approach:
create table private_unread_counter (
from_id text,
to_id text,
count counter,
primary key (to_id)
);
I have one (potentially big) partition for user, and when I want to fetch how many unread messages a user has, I make one select only:
unreadMsgsFromContacts = select (from_id, count) from private_unread_counter where to_id=requestingUser
I'm not sure which one is more appropriate for this. Any answer / info would be appreciated.