How is the “viewed” on messages and tasks for different users done in the database?

Question

SQL

There are tasks if messages. Both can be editable. We store the date of the change. Several different users can watch this, and their viewing rights can be changed.
It is necessary to show the number of unviewed to each user.
My solution is a table where the last view date is stored. At an output on front dzhoynym and there is compared.
user_id | task_id | date_view

For tasks, it's still all right, there are not so many of them, but for messages it seems to me overkill. Are there any better solutions?

Answer 1

Here's what first came to my mind:
I would store
user_id | task_id | is_watched
And I would write a trigger (CREATE TRIGGER) to change the task, so that all subscribed users would change is_watching to false.
And when viewing (already within the application), we change it to true.
In principle, the number of unseen should be quickly considered.