Notification system in PHP

Question

I am building a responsive website same as social networking I have 4 tables checkin friend post notification

Here is structure of notification table

 Notification

 id 
 user_id
 activity_type   
 source_id  
 created_date   
 created_by     
 updated_date   
 updated_by 

Where activity type is a string for example "friendrequest" if someone accepts friend request,"checkin" if someone checkin to some event and "post" if someone creates a post.

Now i store the data in Notification table whenever any of those action is triggered,but i am confused that how should i display the data in a list to logged in users.Data in the notification table will be like this

id  user_id activity_type source_id created_date  
5   1       friend        488       2015-10-13   
6   48      checkin       10        2015-10-13   
7   1       checkin       9         2015-10-13   
8   9       friend        7         2015-10-13   
9   1       checkin       27        2015-10-13   
10  48      post          18        2015-10-13  
11  9       checkin       8         2015-10-13  

Like as per the activity_type the source_id will connect to either of event,checkingor post table. So which query should i use to display notification correctly.

And how do i control the notification fetch,like any activity should only be displayed to the user's friends only.

I used this query but its not working properly

SELECT
    group_concat(`user_id`) as 'users',
    `activity_type`,
    `source_id` as 'post'
FROM
    `notification`
WHERE
    `source_id` IN ("488") 
GROUP BY
    `source_id`,
    `activity_type` 
ORDER BY
    `source_id` 

Let me know if there are any example projects available online as well From the answers i got here i wrote this query Let me know if it can be improvise any other way

 SELECT 
      notification.*,event.title 
 FROM 
      notification 
 INNER JOIN 
      event 
 ON 
      event.id = notification.source_id 
 AND 
      notification.activity_type = "checkin"
 WHERE
      notification.user_id in (488,489) 
 UNION 
 SELECT 
      notification.*,user.firstname FROM notification 
 INNER JOIN 
      user 
 ON 
      user.id = notification.source_id 
 AND 
      notification.activity_type = "friend"
 WHERE
      notification.user_id in (488,489) 

What should i do to find the friends of logged in user? Should i write another query like

Select friend_id from friend where user_id=1

and save the array of id and pass it to the notification queries above

Is there any way to do that any other way without writing 2 different queries?

Answer 1

Since source_id field may link to one of 3 tables, you can either

1. Use left join to join these 3 tables on the notification table using source_id field. Based on activity_type you can decide which fields to display from the resultset in the application code. Drawback: if the same ids occur in all 3 tables, then the query will pull unnecessary data.

2. Create 3 queries for the 3 tables where you join each source table to the notification table based on the activity_type field and combine them in a union query to get a single resultset.

3. Depending on the content of the 3 source tables, you may choose to denormalise and include details of the events within the notification table, so you do not have to join to the source tables. For example, if sy becomes a friend of another, then you can explicitly store the friend's name in the notification table.

To display notifications to friends only is relatively easy, all notifications displayed should be originated by friends. You did not disclose the structure of the friend table, but I guess you have 2 fields that relate to the users table user_id field. So, using the friend table you inner join the notification table the way you did fdor the query that displays the friends of a user.