I am working on the some Java based web application. where we need to maintain unread count for specific set of user( which is grouped by some resource say Documents) when someone else posting a comment in an application. For maintaining a comment we are using MySQL. Let me explain the DB schema:
TABLE 1: Document which contain following attributes
ID, DOCUMENT_NAME, SOME OTHER Parameters
TABLE 2: Comment which contain following attributes
COMMENT_ID, MESSAGE, DOCUMENT_ID(foreign key with table 1)
Note: one-to-many relationships in table 1 & table 2.
Now, suppose one user which belong to document 1 posting a new comment. We need to update unread count for other users who belong to document 1. we are thinking that, we will create one new table where we are restoring unread count in below manner:
Table 3: Document-comment-unread
DOCUMENT_ID, USER_ID, UNREAD_COUNT
Whenever user posting a comment for document we will update unread count asynchronously in table 3 using some AWS Lambda. Similarly, If user want to delete some unwanted comment that time we will decrease unread count for all user who belong to particular documents and whenever any user who seen all comments to same documents in that case we need to reset unread count again to 0 for specific documents. This whole approach takes lot of DB operation whenever posting/deleting/viewing a comments. That is quite cumbersome.
Is it there any other efficient way to achieve similar kind of functionality?