I have different 'modules' . Currently , they are:
- Votes
- Chats
- To-do lists
I am using mysql and long polling to get updates about these modules in near real time.
I have also have a table called groups, and users. users and groups have a many to many relationship.
My votes-related tables look like this:
Aside of these, I will be creating a central actions table, that contain UserID , group_ID, and action type. This is to make it easier on the database when long polling. The central actions table will tell the poller if there is any new data for the several modules, and if there is, then the poller will go to the respective module_actions table to get the detailed information.
I am doing this because the number of modules will increase with time. right now it is three, but it can be 10 or even more. I don't want to poll 20 tables every 1 second for every user that is logged in.
In the image that i have provided, there is a table called vote_actions. This will store which user performed what kind of action on which group. Action TYPE can be things like:
- Created_sub_item
- Voted_on_sub_item
- Unvoted_on_sub_item
- Modified_vote_item
These will be predefined by my database(enum values), and depending on the request, a new row with the right action type will be inserted. This works when the user acts upon a vote_sub_item. But I am not sure how to record actions when he performs action on the VOTE itself (like creating a new vote with sub items, changing description etc).