Making a notification system, database strategy question

Need help Database
1

So I am making a notification system (much like how Facebook or Instagram have) for a website, and I’m wondering what would be the most efficient way to accomplish this.

I have a table for Messages, and I can put fields such as (title, message, link, user, senddate, expiredate, status). I could then make a message for each user any time a notification comes up (new comment reply, new course, etc).

That seems the most straightforward to me. That would over time generate a lot of messages, especially as the site grows, so I imagine there would have to be some cleaning done (hence the message expiration, which could be used to schedule a delete of the message).

Another strategy would be to make the same message table, remove the user field, and only create one message (not one per user). Then, attach the message to a message list field created into the user table. New messages can be attached to the user, and can be removed after the user chooses to remove it (click an X icon on a message panel for example).

This would generate much less messages, but would be adding lots of messages to users. The list of messages on the user table would not conceivable get bigger than 10-20 messages. However, expiring messages would be trickier, because I couldn’t just delete the message as it would have a soft reference. I would need to remove it from all users who hadn’t removed the message yet before deleting it. I would also lose the ability to have status (message read, clicked, hidden, etc) since there would only be one message.

So functionally it seems better to go with the first strategy. I’m just wondering if there is a performance difference between the two options.

2

Do the first strategy, let that database fill up :partying_face:

3

That was what I was going to do, as functionally its a better fit for sure, but my question is more contrasting the difference in performance between those two strategies.

4

Do the 1st option, too much messing around with list fields in the 2nd option + you said it yourself

Also list fields have race condition issues if two different workflows are adding to a list at the same time. So two notifications coming in at the same time, both adding to the Current User’s list of Messages, you could have issues.

One example: "Add to list" and race conditions

Don’t even bother cleaning up old Messages, let them pile up into the millions then if you notice even a slight lag (doubt it) you can deal with garbage collection later

1 Like
5

Thanks for elaborating!

1 Like
6

I should mention make sure you stick to serverside searches, meaning using constraints as much as possible. Pretty much any other operator after the search will require the client browser to download all the search results then filter further after that, If you asked someone’s browser to download 1 million messages just to show 4, it would catch :fire: :crazy_face:

If you ask the server using constraints to find all unread messages, boom, done in an instant.

1 Like
7

Don’t follow the second strategy. Go with the first one. I would call it notification_inbox table, with userId column. And it could have status fields like seen_at, clicked_at, archived_at etc.

But eventually, this will become difficult to manage anyway. You could also try third party plugins like Engagespot - Engagespot Notifications Plugin | Bubble