CODEWITHSUNDEEP
phpandroidmysqlnotifications
Sainath Krishnan
Sainath Krishnan

Reputation: 2139

Notifications system with MYSQL and PHP

My android app talks to the MySQL backend using a PHP API. The app allows readers to follow different authors, and get notifications when one of the authors they are following, submits an article.

Right now I am doing this by storing a new record for each each of the author's followers. i.e : If an author with 1000 followers submits an article, it adds in 1000 new rows, with the seen column set to FALSE.

Now when the mobile app requests unseen notifications, I can show just those records, setting seen to TRUE.

However, this scales with some difficulty. An bunch of authors with 300k+ followers, for example, will create a large number of rows.

Is this simply the cost of such a system? Or is there a more efficient way to handle this, perhaps from the app's side? 

Table : posts (Only the relevant columns)
-------------
id : INT(10) PK,NN,AI
author_id : INT(10) NN,UN


Table : followers
-----------------
id : INT(10) PK,NN,AI
author_id : INT(10) NN,UN
user_id : INT(10) NN,UN

Table : notifications
---------------------
id    :  INT(10) PK,NN,AI
user_id : INT(10) NN,UN
author_id : INT(10) NN,UN
seen : VARCHAR(1) NN

A few Table Notes:

  1. Every time a user follows an author, a new row is created in the followers table with the user_id of the reader, and the author_id of the person being subscribed to. Total rows at any point will be equal to the sum of the follower counts of all users.
  2. When an author submits a post, one new row gets created for each of their followers, in the notifications table, with the seen column set to FALSE. The app then sends another call, to mark them seen. Does the job, but a huge amount of data gets added in constantly.
  3. When the app requests notifications, all the unseen rows from the notifications table that pertain to the current user, are retrieved.

Upvotes: 1

Views: 482

Answers (1)

Gabe Sechan
Gabe Sechan

Reputation: 93561

There's a lot of ways you can do this better. Here's one simple way- you can use a timestamp. Keep track of the last time a user requested notifications. Then when they next request notifications, query for all the articles with a timestamp later than that time. Those are his notifications. Requires either 1 row per user, or 1 column per user depending on if you make a new table or just a new column for last check time.

Upvotes: 2

Related Questions