How to design database tables to implement notifications like on toster.ru or stackoverflow?

Question

MySQL

Advise how you can implement the database structure to notify users of new posts on the site with a minimum number of requests to the database?
The case is not going well. I sat down to figure it out and created a notifications table with postid, userid columns. Added a notify column to the user profile, if the value is 1 - we check notifications, 0 - we do not make requests (honestly, I doubt it).
For example, the user went to the page with id = 2. Check if the user needs to be notified, if so, check the user id (userid) and page id (postid) in the notifications table, if it exists, delete the line (we have already notified the user and we it is not needed).
Am I going the right way? And how to correctly check all rows in the notifications table for the presence of a specific user in them, so that if he is not there, mark this in his profile? By correct verification, I mean a minimum of operations with the database and timely verification.
Many thanks to everyone for the advice!

Answer 1

Alternatively, create a table that will store the user id, post id, number of comments, and the date the post was last accessed.
With such a structure, you can always get a list of what was viewed, select posts by id from this list and compare in which of them the number of comments has changed. There are two requests in total. Also, when displaying posts, you can get a list of "viewed", compare the number and mark posts with new comments.
You can speed up using some key-value storage instead of a base.

Answer 2

One notifications table with unique id and key user_id and enable is enough; and the fields in the notify table in the users table. When a mention is created, we add a row to the notifications table and check notify in the users table. When we look at notifications, we change notify in the users table and enable in notifications.