+ 4

Suggestions on how I can allow users subscribe to a particular thread and get notifications? Like Sololearn

I want to allow users subscribe to a topic by choice and get notified if a new post has been added to that thread. Am using php in development! I was thinking of using MySQL table triggers, by creating an object to notify users ones a new post is added to say the post table! Anyother bright suggestions?

10th Mar 2018, 11:16 AM
Strange
Strange - avatar
33 Answers
+ 3
@Mario, I'm sorry if it troubles you : ) In the case of a user subscribes to multiple threads then there will be multiple records for the said user, each record differs by the thread Id, so we can go SELECT * FROM subscriptions WHERE user_id = <user_id> and we get all the Ids of the threads s/he subscribed (I hope I make some sense here), then of course you can get the thread details from [threads] table having the Id at hand. Pretty much like that I guess... (Edit) The format in [subscriptions] data follows the referred format used in [threads] and [users] table I suppose, because the fields in [subscriptions] refers to those tables.
12th Mar 2018, 7:15 AM
Ipang
+ 2
@Mario, I don't know if this is more of a help rather than complication, but here's the rough idea: * Users table Stores users information [users] user_id user_name ... * Threads table Stores threads information [threads] thread_id user_id -- refer to [users] (owner) timestamp -- thread creation date/time title message replies -- total replies/comments up_votes -- total upvotes down_votes -- total downvotes ... * Thread subscription table Stores thread subscribers information. Also can be used for deciding which user(s) gets notifications. [subscriptions] thread_id -- refer to [threads] user_id -- refer to [users] * Thread votes Stores thread voters information. You can create trigger here to update <up_votes> and/or <down_votes> field in [threads] table. [thread_votes] thread_id -- refer to [threads] voter_user_id -- refer to [users] vote_type -- condition {0=down, 1=up vote} * Thread posts/replies table Stores thread replies information. You can create trigger here to update <replies> field in [threads] table. A new reply to a thread also add new record in [subscriptions] table to allow the thread commenter to subscribe to the thread. [posts] thread_id -- refer to [threads] user_id -- refer to [users] post_id timestamp -- post creation date/time message ... * Thread posts/replies votes table [post_votes] post_id -- refer to [posts] voter_user_id -- refer to [users] vote_type -- condition {0=down, 1=up vote} Hth, cmiiw
12th Mar 2018, 4:45 AM
Ipang
+ 2
nice one Ipang, it's a complication in the sense that am looking for the best approach. I get your idea, but in what format can I store the subscription data? according to your approach am creating a subscription table and a single user will have a single row. Now if a user subscribe to multiple threads, are you suggesting I store the data as in my above approach or how?
12th Mar 2018, 7:01 AM
Strange
Strange - avatar
+ 2
what am trying to avoid is a multiple row per user, because a single user can subscribe to say 5 threads on the average and if all active users do this and their total is say 20k, then it's 100k rows already, that's why I suggested the above with krOw. please check it out and see if this multiple row is still the best approach.
12th Mar 2018, 7:26 AM
Strange
Strange - avatar
+ 2
seriously Ipang, am not an expert too, but we all have to try. the notification should be a normal method that checks all the users userNotify column and alerts the user of the activity according to the thread ID in the userNotify column.
12th Mar 2018, 8:21 AM
Strange
Strange - avatar
+ 2
And I suppose I can do something similar for upvote and down vote notifications
12th Mar 2018, 8:25 AM
Strange
Strange - avatar
+ 2
Alright @Mario, I'm glad you found your solution. Best of luck : )
12th Mar 2018, 8:45 AM
Ipang
+ 2
thanks Man
12th Mar 2018, 8:46 AM
Strange
Strange - avatar
+ 1
In similar way, you can build a table per thread that contains all subscripted members id... At new post in thread, you check this table and send notification to all member... Oblivious you must handle if user is not logged
10th Mar 2018, 12:14 PM
KrOW
KrOW - avatar
+ 1
creating a table per thread will result to numerous tables, imagine Sololearn uses that if there are 1M questions it means you would have 1M tables, that is enough to affect the performance of the system. what do you think?
10th Mar 2018, 12:17 PM
Strange
Strange - avatar
+ 1
Table must be created if least one user follow thread and will be updated only on thread modification and If data is simple you can store this table in other formats also (json, xml or custom) and some caching is useful (in threads frequently updated you can store in memory for fast access to data), You must handle a queue-like data structure for notifications in efficient and error-handling manner... Oblivious you must considerate how much threads/user are, server specs etc.. anyway i m not an expert but o think that this problem is solved yet... try to search on google
10th Mar 2018, 1:01 PM
KrOW
KrOW - avatar
+ 1
yeah
10th Mar 2018, 1:30 PM
Strange
Strange - avatar
+ 1
@krOw I had a thought... I can create 2 column for users table say userNotify,userSubscription and if a user either posts(which is auto subscription to the thread) or voluterily subscribe to a thread, a handler updates usersubscription with the thread ID. A second handler is called by every thread, this can be a table trigger object. when a new post is inserted to the thread, the handler looks for all users with usersubscription column that contains the current thread. if it returns true, the handler updates the user's userNotify column with the thread ID. A third handler checks all userNotify columns and alerts the user that there is a notification per that thread.
11th Mar 2018, 12:57 PM
Strange
Strange - avatar
+ 1
userNotify and usersubscription will carry varchars, say1,2,3 which are thread IDs and will be accessed as an array by the handlers, the notify handler will remove the thread ID from userNotify ones the user responds to it... How bad is that approach?
11th Mar 2018, 12:59 PM
Strange
Strange - avatar
+ 1
lemme break it down... Table users contains userId userNotify and userSub Table threads contains threadId if a user subscribe to say thread with ID 4, then the subscription handler inputs '4,' into userSub. if same user subscribe to another thread ID 2, subscription handler does same and include '2,'. which means the userSub contains 4,2 data. now if there is a new post inserted inside the thread with ID 4, the notify handler inputs 4, into userNotify column. so for this user has the values userNotify (4,) and userSub (4,2) in his row... the reason for inputing the IDs with the comma(,) is to allow the handler access them as elements in an array! so the notification handler checks the userNotify column and alerts the user of the notification and the thread which has allocated it, this handler will also be responsible to deallocate it.
11th Mar 2018, 7:21 PM
Strange
Strange - avatar
+ 1
@Mario, I understand your concern, I guess your way is feasible too of course, I assume you will be using comma separated thread Ids in the userSubscription and userNotify? that multi rows idea just crossed my mind out of nowhere, wasn't really thinking about records amount concern : ) Anyways, I have no experience with notification systems, can you tell me what you are using for the notification? I think I need to learn that too.
12th Mar 2018, 8:15 AM
Ipang
+ 1
I think this is a great idea, it can allow me define the notification type easily. But does it solve the problem of multiple rows? I don't think so.
13th Mar 2018, 12:05 PM
Strange
Strange - avatar
+ 1
because it's easy to get a system up and running. but the problem is maintenance. if say you have 200k users with an average of 5 threads subscription. per multiple rows would be a million rows. for such large schema Quering data would take longer, but not too long though. yet if we look at the entire system together , it will compromise several queries, such complexities can reduce performance and make maintenance slow.
13th Mar 2018, 12:35 PM
Strange
Strange - avatar
+ 1
actually I don't think there should be reasons to distinguish between notifications and pending notifications , a pending notification is one that a user hasn't read, so there's no need to create a special table for it. All we need is handler that alerts the user for notifications and marks it as unread, and if the user reads it the handler removes it from notifications.
13th Mar 2018, 1:41 PM
Strange
Strange - avatar
+ 1
ok but your systen must handling either?? For example SL handle either because real-time notifications and notiications history. You doing same?
13th Mar 2018, 1:59 PM
KrOW
KrOW - avatar