1

I am developing a social networking site like Facebook. I am confused how to create structure for notification table. Should it be separate for each user or a huge one for all-where records added and deleted frequently ?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
phpDevlpr
  • 11
  • 1
  • 3
  • i have created posts and profile and photos etc , tables till now. But i am confused for notification. See i thought it two ways - 1. Small notifications table for each user and a huge table storing all notification together . eg. Not_a11 (id, notf_id) . Notification _master (notf_id, memid, notif_text, dttime) . This way i will be updating both tables all the time. Whenever user logges in his table will be refered and notifi will be displayed. What you say – phpDevlpr Mar 04 '13 at 04:52
  • is anybody there to reply ? – phpDevlpr Mar 04 '13 at 10:38

1 Answers1

3

I have the same problem as you and found this (found this) upon researching where the table structure given is :

id             
user_id       (int)
activity_type (tinyint)
source_id     (int)  
parent_id     (int)
parent_type   (tinyint)
time          (datetime but a smaller type like int would be better)

where:

activity_type tells me the type of activity, source_id tells me the record that the activity is related to. So if the activity type means "added favorite" then I know that the source_id refers to the ID of a favorite record.

The parent_id/parent_type are useful for my app - they tell me what the activity is related to. If a book was favorited, then parent_id/parent_type would tell me that the activity relates to a book (type) with a given primary key (id)

I index on (user_id, time) and query for activities that are user_id IN (...friends...) AND time > some-cutoff-point. Ditching the id and choosing a different clustered index might be a good idea - I haven't experimented with that.

Pretty basic stuff, but it works, it's simple, and it is easy to work with as your needs change. Also, if you aren't using MySQL you might be able to do better index-wise.

It also suggested there to use Redis for faster access to the most recent activities. With Redis in the mix, it might work like this:

  • Create your MySQL activity record
  • For each friend of the user who created the activity, push the ID onto their activity list in Redis.
  • Trim each list to the last X items

Redis is fast and offers a way to pipeline commands across one connection - so pushing an activity out to 1000 friends takes milliseconds.

For a more detailed explanation of what I am talking about, see Redis' Twitter example: http://code.google.com/p/redis/wiki/TwitterAlikeExample

I hope this might help you also

Community
  • 1
  • 1
YXN
  • 106
  • 1
  • 6
  • Giving just a link reference as an answer is not good. In case the link get changed/updated/edited/removed, the answer will automatically become invalid. So always try to explain the answer and then give a reference. – Sankumarsingh Aug 20 '13 at 09:59