How to implement a counter of new posts for each user?
There are several those (communities) where users post their posts. Each user can be subscribed to only one topic. How to count the number of new posts in the thread to which you are subscribed for? Code not needed, I have the algorithm and the structure of the database, and the fact that to mind nothing comes.
A separate subscriptions table: user_id, post_id, is_read
When you create a new post, the trigger, the database needs to everyone who is subscribed to this topic, create a record with the flag "unread".
Well, the counter is to count for each user_id.