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.

Answer 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  

Answer 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
 }

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