PHP News Feed Database & Design

Question

I'm designing a News Feed system using PHP/MySQL similar to facebook's.

I have asked a similar question before but now I've changed the design and I'm looking for feedback.

Example News:

User_A commented on User_B's new album.

  "Hey man nice pictures!"

User_B added a new Photo to [his/her] profile.

     [show photo thumbnail]

Initially, I implemented this using excessive columns for Obj1:Type1 | Obj2:Type2 | etc..

Now the design is set up using a couple special keywords, and actor/receiver relationships. My database uses a table of messages joined on a table containing userid,actionid,receiverid,receiverObjectTypeID,

Here's a condensed version of what it will look like once joined:

News_ID | User_ID |                  Message                   |     Timestamp

  2643       A       %a commented on %o's new %r.                  SomeTimestamp
  2644       B     %a added a new %r to [his/her] profile.         SomeTimestamp
                         

%a = the User_ID of the person doing the action

%r = the receiving object

%o = the owner of the receiving object (for example the owner of the album) (NULL if %r is a user)

Questions:

1. Is this a smart (efficient/scalable) way to move forward?

2. How can I store a "Preview of the event"? For example, if I want to show the comment that User_A made to User_B (like above, and on facebook's news feed). I have considered using an encoded copy of only the relevant data.. for example JSON encoding the comment text or photo html.. but this seems fragile (the user may delete the photo while it's still in another users' feed)

3. How can I show messages like: "User_B added 4 new photos to his profile." with thumbnails of the photos?

Answer 1

Having built something similar just recently, one thing I would suggest is to separate the idea of how to store the data from performance. In my case, the users need to be able to go back and look at news from any time period, so arnorhs' assumptions don't work (regardless, there's no reason to store HTML if you don't have to-- leave the formatting outside).

What I found was that I store the stuff in a couple of classes, ActivityType and Activity. ActivityType holds the format for the message (like your '%a commented on %o's new %r') and an indicator of whether it represents actual activity or a comment on someone else's activity (so I know which object to link to, the actor's activity or the actor of the activity commented upon) and Activity stores the actor, victim, primary key of the object, a primary key to the commented-upon object if it exists and the timestamp of when it occurred.

Which is all great and results in nicely-normalized data. Which slows to a crawl as soon as you have a half-dozen friends (performance is complicated by the fact the whole thing is location-based so I'm looking up the distance each that user is away from you). Everybody is looking for an excuse to play with NoSQL storage systems now, but this is actually a good one. You're going to have to de-normalize the hell out of the data to get decent performance from a relational database. And the stuff's hard to cache due to the various intersections of relationships. Think about storing the data in MySQL but getting it back out of a NoSQL storage system.

Answer 2

I have a similar problem and similar question here on Stackoverflow - our questions look almost the same :) Check it - getting JSON data-tree from MySQL

But I'm trying to solve the problem in a little different approach: I'm creating JSON objects. So my newsfeed table looks like this:

news_type   | datetime_added  | params
------------+-----------------+--------------------------------------------------------
new_photos  | 2010.12.01      | {user_id: "12", photo_id: "26", photo_url: "/images/photo.jpg"} 
new_comment | 2010.12.01      | {owner_id: "12", photo_id: "26", photo_url: "/images/photo.jpg", commenter_id: 25, comment_text: "Nice!"}

Then I use json_decode in php to create arrays. Then depending on news_type I create needed HTML.