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

Question

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?

Answer 1

@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.

Answer 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

Answer 3

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?

Answer 4

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.

Answer 5

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.

Answer 6

And I suppose I can do something similar for upvote and down vote notifications

Answer 7

Alright @Mario, I'm glad you found your solution.

Best of luck : )

Answer 8

thanks Man

Answer 9

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

Answer 10

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?

Answer 11

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

Answer 12

yeah

Answer 13

@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.

Answer 14

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?

Answer 15

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.

Answer 16

@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.

Answer 17

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.

Answer 18

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.

Answer 19

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.

Answer 20

ok but your systen must handling either?? For example SL handle either because real-time notifications and notiications history. You doing same?