Want to create a facebook likenews and updates ...

Question

Hi guys, I'm wokring on a simple social network site and I would like to build a simple news update feed. Feed not in the actual sense but you know like those little reports you get on facebook eg when someone posts a picture you get a simple report saying in your main page that - so and so added a picture, or so and so added a comment. Stuff like that one liners.

However I want to build something similar. I was thinking of running a union based query on all my tables but that is INSANELY impractical. Another idea I had was to create a news feed table which would have fields like:

Who - Action - ON WHAT

Where 'WHo' - refers to the user ID of the individual who did something Action refers to the action ie.. adding a comment WHAT refers to like if the action was done ON something like a comment passed on an article.

However I'm not so sure if this is a good idea... I want a simple solution - any ideas would be much appreciated.

Answer 1

I think this sort of depends on what sort of actions you are expecting to be performed on your items. I'm no expert, but I think the approach I would take is to keep each action distinct.

Let's assume that you have news items to display in your feed, and users can vote on them (or even just 'Like' them a la Facebook) or add a comment about the item.

I'd likely set up my database as such:

NewsItems
---------
NewsId
UserId (if this is like Facebook where it's someone posting their item)
Body
Timestamp

Votes
-----
VoteId
NewsId
UserId
VoteType (or possibly VoteValue with values +1 and -1 or something)
Timestamp

Comments
--------
CommentId
NewsId
Body
Timestamp

Using this, you can retrieve the last n items that a user posted from the NewsItems table, and as you display each, you can use it's NewsId to determine it's current vote count from the Votes table, and also use NewsId to retrieve a chronological listing of all comments made on the item.

I suppose you could also replace the Body field in NewsItems with two other fields, like NewsType and TypeId. The former tells you which table to use to lookup an action (since you probably don't want picture BLOBs and status update text in the same field/table. The second gives you the key to lookup in that table.

Just my two cents. Hope it helps.

Answer 2

thats a tricky and not so easy thing to do. I worked for a start up social network and it was something they wanted as well. I dont think i still have the code laying around but if i recall correctly i went about it something like this

DB:

USERS

id : guid "a unique identifier for this user" "other user info"

ACTIONS

when : unix_timestamp who : guid "the user who made the actions guid" type : set('image','news') "replace with a list of the type of things you want to track" what : url "not like a web address but the guid of the thing that was made"

FRIENDS

id1 : guid "one of the 'friends' guid's id2 : guid "the other persons guid

PHOTOS

id : guid "a unique identifier for the image" url : varchar(255) "where is the image stored (file name directory etc) who : guid "the user who posted the photos guid" "other info you want to keep track of"

NEWS (think status update)

id : guid "a unique identifier for this statu update" who : guid "the guid of the person who posted this" when : unix_timestamp "timestamp of when it was posted what : text "the contents"

using the above structure i would have my code make an entry into the ACTIONS table anytime a user posted a photo or a status update. then when their friend logged on it would go through the ACTIONS table pulling out all the actions of anyone it found was friends (via the FRIENDS table) the TYPE field is used to differentiate what table to use when linking the IDs of the actions. so if the person posted an image when it writes the action to the screen it can set the link up to point to whatever script your using to display images. etc etc

ill see if i can find the code, if i can ill post it (company went under and i retain ownership of the code)

If my explanation isnt clear ill take some more time later to better document the process and code.