Creating 'feed' of activity--most efficient setup?

[amedhussaini]

Hi everyone,

 

We have a social networking iPhone application that is constantly making API calls to our server in order to bring in information and write information to the database.

 

Every time a friend is added, a comment is created, etc (basically in a bunch of different tables things are being created)--we want this stuff to show up on one screen, like a facebook 'feed'.

 

I've thought about approaching this two ways, and I'd like some advice from the experts out there on the more efficient way to do it.

 

1. a lot of DB writes--in other words, everytime a user does something that does belong in the feed (like comment on something, or like something), at the same time the api can add a row to a new 'notifications' table.  that way, when i need to pull a feed for someone later on, i can just pull from this one table

2. one big read--currently, i don't have it setup like the above--the plan was to just query all the tables when u want to refresh your feed.. my api will return a big xml doc to the iphone and then the iphone will rearrange the data as needed

 

 

what do you think is the better approach?

[gizmola]

I personally would lean toward the notification approach.  If you have a solid generic design for you notification table, this will work fine, and also can be sharded effectively, since the SELECT needs to find activity for the user in question, and there doesn't sound like much of any need for searching across shards.