Social Network News Feed Database & Design
        Posted  
        
            by pws5068
        on Stack Overflow
        
        See other posts from Stack Overflow
        
            or by pws5068
        
        
        
        Published on 2010-05-14T14:53:00Z
        Indexed on 
            2010/05/14
            14:54 UTC
        
        
        Read the original article
        Hit count: 419
        
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 Notifications:
User_A commented on User_B's new album.
"Hey man nice picture!"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 is designed for efficiency - using 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:
- Is this a smart (efficient/scalable) way to move forward? 
- How can I show messages like: "User_B added 4 new photos to his profile."? 
© Stack Overflow or respective owner