title: Notification/News feed with PHP and MySQL
body: <p>I have been working on a complex PHP system that incorporates elements from the idea of Social networking but for a closed group of people. In place I have a few modules, photo and video galleries, complete commenting system on every module and submodule, private messaging, personal emails with a GUI and many more. </p>
  
  <p>My problem is that no matter how hard I try I cant seem to design the backend for the notification and news feed part, something like what facebook has, in an <em>efficient</em> way. The modules are pretty much event driven so connecting them to the notification system shouldn't be a problem. Hopefully some group brainstorming will give an end to my problem.</p>
  
  <p>I will only be addressing my concerns on the notification part in this post (it'll become a very long and chaotic post should I include the news feed as well).</p>
  
  <p>Here is my first MySQL table draft.</p>
  
  <pre>
  <b>notificationID</b>
  Primary key
  
  <b>notificationModule</b>
  Module type foreign key - photo, video, comment, message
  
  <b>notificationConstructor</b>
  Foreign key of the element (which is of type "notificationModule") that triggered the creation of this notification
  
  <b>notificationUser</b>
  The user that this notification is aimed towards
  
  <b>notificationTime</b>
  Time at which the notification was created
  
  <b>notificationFlag</b>
  Notification has been read flag
  </pre>
  
  <p><strong>Possible Problems / Conflicts</strong></p>
  
  <ul>
  <li>The event of a user commenting on a photo two different users have already commented on will trigger three notifications? One for the photo uploader and one for each of the commenters? (affecting <b>notificationUser</b>)
  <ul>
  <li><b>notificationTime</b> is the creation time. Following the above issue, should we not create a new notification this field should be replaced by <b>notificationUpdateTime</b> or maybe exist aside it?</li>
  </ul></li>
  </ul>
  
  <p>I'm aiming towards less code complexity and more database efficiency. Trying to separate the database layer from the code layer puzzled me on this part of the site :(</p>
  
  <p>I'm open to all concerns and ideas.</p>