Design pattern : notification system

Question

I'm working on a website that will use features of social networking (like facebook for example).

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.

I wonder what is the best solution :

• Solution 1, aka "logging".

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.

Good : easy to code, separation between notification feature and "normal" features, not too much resources-consuming when i need to read the table.

Bad : 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.

• Solution 2, aka "look everywhere".

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.

Good : Not a too big table compared to solution 1, no "redundancy" of information.

Bad : 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.

Can you please tell me what you think the better and why, or do you have a solution i didn't imagined ?

Thanks =)

---

Edit : Let's say i use a really basic DB design : users have friends, can do quizzes.

1 table for user list, quizz list,

1 table quizz<->user relation,

1 table user<->user for friendship.

Everytime a user visit his own profile, he can see what happened : new quizz<->user relation, new user<->user relation, etc. How would you design a notification like that ?

Answer 1

Create a system queue, each message added to this queue has a list of "consumers" and the content. The main message pump processes each message and sends the message to all consumers.

Lets say 2 people befriend each other. You add a message to the main system queue that A is friends with B and consumers are both A and B. When your message "pump" (processor) sees this message it adds it to the queue of A and the queue of B. So now user A and user B have a new message that they are friends. In turn each user has a message processor, so when it sees a message called "I am friends with [someone]" it processes this as add a new entry to the "wall" visible to friends of A that "A is friends with B", etc.

This is overly simplistic but hopefully shows how message queues can be used for this (very similar system is used as the windows UI framework) so there is already an existing example and there are plenty of synchonized message queue patterns you can use.

Rest is up to you to design.