What is the best way to organize the notification system?

Question

PostgreSQL

There is a project, similar to a social network, it has: subscriptions, comments and likes to posts.
The question arose about adding notifications, the first thing that came to my mind was to store notifications in tables, here is an example:

But I came across the following approach:
In the posts/posts_likes/users_subs/users_followers tables, add the status=new|read column and determine how many notifications are not was read and actually given to the user.
Obviously, the second option is more beneficial in terms of memory in the database, and new tables do not need to be created. Are there better options for organizing the notification system?

Answer 1

And what will happen to several different types of devices?)
In general, throw out the sender, add the notification type to the main table, store the data in a serialized form, or even take a non-relational database. And in general it should work on queues. Or do you really need to display a list of recent notifications somewhere separately? (which is doubtful)

Answer 2

Notification - not sent by the user, but by the system. Therefore, the senderUserId field is redundant, as is receiverUserId.
There should be only 5 fields:
id:uint,
type:uint,
userId:uuid,
isRead:bool,
created:timestamp
And who exactly sent and which notification, we find through the notification_type table of notification types.
And there: id and notifycation_table (you have 4 of them. those on the left).
Using id (notifications), notifycation_table and userId (for whom notification), we find at once all the details of the desired notification.
That is, we search from the general table of notifications in 3 iterations: "immersion" of the selection (there will be one request, of course, but it will be executed much faster), and not vice versa (as you have done now).

Answer 3

I use Firebase and requests to it from the server. This is if notifications are PUSH.
If notifications are inside the application, web-socket is better here.