I am designing a system for notification with following scenario. Consider the following database schema Record
record_id - pk
record_name _ String
lastModified _ date
I have another table called user which has user id as primary key.
Each user is shown the records at the frontend.
Now there is an external tool which will update the Records table directly(not programatically) with certain records(add/update)and change the last modified date. I need to notify end user about the change by showing the record in record listing with a different color. Each user will have to acknoweldge the change by doing as "Mark as read", clicking on which the notification is no longer shown to the user
My Proposed Solution
- Create another table called "Modification table" which will have last modified date maintained. The initial value will be current date. This date will be stored in memory as well(either ehcache or memcache)
- As soon as records table gets updated with new or updated record, create a trigger to update "Modification table" with updated date.
- Write a scheduler (Quartz job) in Java, which will see what is the current date in Cache and fetch all the records from database whose lastModified date is greater than current date of memory.
- Insert all these records in a separate table called "userNotifications" which has these columns
notificationId - pk
status - read/unread (boolean)
- That is for each user a record will be added in the above table for each record. If there are 10 records updated and 2 users in the system, there will be 20 records.
- When user clicks on mark as read , either the record will be deleted from notification table or the status flag can be updated to read.
I feel that there is a flaw in above design and fear that thsi will break the system if the number or users and records are large. Do you think the same? Can anyone suggest any other possible design options?