How to structure database to handle unread messages in a discord like app

Hope this is the right category since Eloquent is db related.

Trying to figure out how the relationship might work on Discord between Channels & Users.

For example.

A server has 10 channels
A server has 1000 users.

In order for every channel to notify a user if there is unread messages, there would need to be ChannelUser pivot?

channel_id
user_id
last_time_read_at (datetime)

And check if a channel-> last comment created_at compared to the channel->user->last_time_read_at to determine if there is unread messages or not.

I know this works, but trying to see if there is some other way to handle this that might be more efficient. As if a server grows to 10,000 users, and has 100 channels. This would essentially mean 1million rows just for notifications for unread messages? And if you create 1 extra channel, you need to also create 10k ChannelUser records.

Is there some other way that this might be handled that I am not seeing?

Just exploring ideas, so any insight or thoughts are much appreciated :)

Jakub003

2 years ago

Level 7

After posting this lol... I think I might have figured it out.

Have a ServerUser relationship and than have one of the columns be a json that has channel_id and time stamp of last read at. That way, you are just doing 1 simple query

{
 {'channel_id' => '1' , 'last_time_read_at' => '34343535'},
 {'channel_id' => '2' , 'last_time_read_at' => '34343535'},
 {'channel_id' => '3' , 'last_time_read_at' => '34343535'},
 {'channel_id' => '4' , 'last_time_read_at' => '34343535'},
...
}

So it would be like

server_id foreignid
user_id foreignid
channels_last_read_at json / longtext?

Still would like to see if there can be other solutions

PovilasKorop

2 years ago

Level 11

@jakub003 I think you have the wrong assumption that you need to store all channels and all servers for all users.

If there are 1000 users, not all will be active and not all will actually read all channels. So in case of 10,000 users and 100 channels, there won't be 1 million rows. There will be close to 10,000 still. If some user doesn't read some channel, you just don't have a row for it in DB.

With that, you don't need to experiment with the structure, you just query from that DB table without any json. Cause if you go to json, you will save space but your queries will become slower if you want to perform where() within json.

1 like

nexxai

2 years ago

Best Answer

Level 37

An alternative to any other structure is to just track which messages a user saw last (e.g. successfully delivered to the client), and then whenever it asks for more messages, you can do a Message::where('channel_id', $channel_id)->where('created_at', '>', $last_seen_date)->get(); kind of thing. Leave it up to the user's client to tell you where they saw up until.

2 likes

Please or to participate in this conversation.