1

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?

Deven Soni
  • 11
  • 3
  • There is one flaw with your current design: you are not maintaining which user read which comment. To give you an example: user A posts a new comment, which at the moment is not read by users B & C. Then user B reads the comment. Then user A deletes the comment. Since you do not maintain that user B read the comment and user C did not, if you just decrease the unread count for both B & C users, then the count will be incorrect for B. If you do not decrease the count, then the count will be incorrect for C. So, you need to have another table that shows which user read which comment. – Shadow Feb 25 '22 at 09:20
  • Maintaining this table and using it will add more database operations - as would any other new feature to your application. Whether this is worth it is your call. – Shadow Feb 25 '22 at 09:22
  • Thanks for the quick response. For handling that scenerio what we are thinking we will maintain list of unread comment_id which is unread along with the total unread count for users. whenever user A delete a comment for which is already seen by user B and not by user C. In that case we will decrease total unread count only those users who did not read that particular comment. – Deven Soni Feb 25 '22 at 10:13
  • In this case I would not maintain an unread count aggregate value, but use count() to get the number of unread comments. Denormalisation (this is the technique that you are trying to use) is a valid optimisation technique that optimises selects at the expense of writes (inserts and deletes). You have to decide which is more important. – Shadow Feb 25 '22 at 10:18
  • yeah we are also thinking to remove unread count. That can easily derive from count() method. – Deven Soni Feb 25 '22 at 11:04
  • Do you think any other efficient approach to reduce the DB operation to achieve similar kind of functionality. – Deven Soni Feb 25 '22 at 11:06

0 Answers0