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 ?