Best Way To Build A Multi-Notification System in PHP

Question

I am currently working on a mobile app that lets you ask friends for favourites, it's a HTML5 frontend and a PHP backend. I am stuck as to what the best way is to build a notifications system, more-so the database schema than the actual code itself.

The mobile application flow is as follows:

• A User asks for a favour
• The user can choose to; notify all friends, notify favourite friends or notifdy closeby friends
• Depending on what the user chose, people are notified

What is the best way to do this in PHP and MySQL? I'm not really asking for anyone to write me the PHP code, but rather map out the most ideal MySQL table and fields schema instead as that is what I am currently stuck on. Thanks in advance for your help.

Solution 1

You can create a notification table such as:

from_user | to_user | notification | seen

and then whenever you want to notify a user you just add a record with the needed informations and set seen to 0 / false.

Then when the user reads the notification you set that parameter to 1 / true.

Example:

from_user | to_user | notification | seen
    -          -          -           -

User john notify user jeff:

from_user | to_user | notification | seen
   john      jeff       whatever..    0

User jeff read the notification:

from_user | to_user | notification | seen
   john      jeff       whatever..    1  

Solution 2

Why not just list all the notifications in a table called "notifications"

id | user_id | from_user_id | notification

• id = notification id
• user_id = who is the notification for?
• from_user_id = who sent the notification?
• notification = the message

Then as pseudocode:

// Create a notification from User A to User B
$this->db->insert ('notifications', array ('user_id' => $friends_id, 'from_user_id' => $current_user_id, 'notification' => $message));

 // The meanwhile, on your home page or somewhere, wherever you want to display notifications
 $this->db->where ('user_id', $current_user_id)
 $notifications = $this->db->get ('user_id');
 foreach ($notifications as $notification)
 {
         // Display the notification as needed
         // Optionally delete the notification as it is displayed if it is a "one off" alert only
 }

Solution 3

Extending Jeffrey's Answer :

id  |  from_user | to_user | notification | seen | timestamp
 -         -          -          -           -         -

timestamp will help you to identify at which moment particular notification occurred. You may also able to create time moment by extending your script with timeago.js or Livestamp.js

It will also help you to create a notification timeline and maintenance of notification would be simpler.

Comments

[Dwayne Charrington]

So basically if the user chooses to notify favourite friends, I'd firstly do a query to get the user ID's for the current users favourites, then do a bulk insert into a notifications table for each of those ID's? It seems logical enough. Something like this did cross my mind originally, but I thought perhaps there was another way. This seems like the logical thing to do.

[Dwayne Charrington]

Thanks for answering mate. I went with Jeffrey's answer as he posted first, but I appreciate you taking the time to respond so I up-voted you.

[Shoe]

@Dwayne, yeah. Or you could do a subquery (dev.mysql.com/doc/refman/5.5/en/subqueries.html). In any way this seems the most logical and clean way to do this. I would also delete all the seen notification that are older than 7-14 days to optimize everything, but that's up to you.

[Mohamed Said]

A simple but reliable approach +1