title: Design pattern : notification system
body: <p>I'm working on a website that will use features of social networking (like facebook for example).</p>
  
  <p>I would like to implement a notification system which shows stuff like "X added you as a friend", "Y invite you to the party", "Z has taken the lastest quizz"... and i don't know how to do.</p>
  
  <p>I wonder what is the best solution :</p>
  
  <ul>
  <li>Solution 1, aka "logging".</li>
  </ul>
  
  <p>A dedicated table "notification". I add rows in this table everytime something that rise a notification happend (friend adding, quizz answering, etc.). The table "notification" has fields that contains different information, according to what kind of notification is added to the table.</p>
  
  <p><strong>Good</strong> : easy to code, separation between notification feature and "normal" features, not too much resources-consuming when i need to read the table.</p>
  
  <p><strong>Bad</strong> : Notification table will grow probably very big (i think I will add 10k rows/day in the table), "duplicated" information : informations in the notification table can be found in all other table using date/list/whatever comparison.</p>
  
  <ul>
  <li>Solution 2, aka "look everywhere".</li>
  </ul>
  
  <p>Everytime i need to show the notification list or the show how much new notifications there are, I look to all the concerned table, compare date/etc to know if something new happened since the last time the user checked the notification.</p>
  
  <p><strong>Good</strong> : Not a too big table compared to solution 1, no "redundancy" of information.</p>
  
  <p><strong>Bad</strong> : I am affraid because of the number of user (~1k+), it make the server explode because it is resource/time consuming, little harder to code/maintain.</p>
  
  <p>Can you please tell me what you think the better and why, or do you have a solution i didn't imagined ?</p>
  
  <p>Thanks =)</p>
  
  <hr>
  
  <p><strong>Edit :</strong>
  Let's say i use a really basic DB design : users have friends, can do quizzes. </p>
  
  <p>1 table for user list, quizz list, </p>
  
  <p>1 table quizz&lt;->user relation, </p>
  
  <p>1 table user&lt;->user for friendship.</p>
  
  <p>Everytime a user visit his own profile, he can see what happened : new quizz&lt;->user relation, new user&lt;->user relation, etc. 
  How would you design a notification like that ?</p>