Notifications table design?

Question

I'm trying to create a notification system for my social network. But I'm stuck on the database design. so when a user comments on another user I want to display "X commented on your post." or when someone follows another I want to display a notification.

This is my table so far:

CREATE TABLE IF NOT EXISTS `notifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `notification_id` int(11) NOT NULL,
  `text` text NOT NULL,
  `read` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

The part where I'm confused at is, Should I insert a record when someone follows another person? Like... Right after when someone clicks follow button? If so, What should I insert to the row?

And I'm using PHP with Laravel framework.

Answer 1

Since this is an open-ended question with no perfect solution, I would recommend the following structure:

Events table

id    type          text
-----------------------------------------------
1     comment       commented on your post
2     follow        followed you

etc

Then the notification table would be as follows (I couldn't think of better field names to convey the idea):

Notification table

id      user_to_notify       user_who_fired_event  event_id    seen_by_user
--------------------------------------------------------------------------- 
 1            12                 13                    2        yes
 2            13                 12                    1        no
 3            1                  15                    1        yes

seen_by_user could be boolean.

The advantage of using the above structure is you can answer the following queries without complex SQL queries:

1. Total comments on user X's post by user Y.
2. The pattern of users reading or clicking on notifications. e.g., he may not click on the notifications of people following him.
3. If a user has given a preference not to be notified about certain events, you can filter it out based on event_id.

As soon as X comments on Y's post, you make an entry in the notification table and then an entry into the comments table with the comments. I hope this helps in some way!