News Feed Database Design Efficiency

Question

Greetings All, I've seen similar questions asked before with no conclusive or tested answers.

I'm designing a News Feed system using PHP/MySQL similar to facebooks. Seeing as this table could grow to be quite large -- any inefficiency could result in a significant bottleneck.

Example Notifications: (Items in bold are linked objects)

User_A and USER_B commented on User_C's new album.

User_A added a new Vehicle to [his/her] garage.

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

It works but I fear it's not nearly scalable enough, now I'm looking to object serialization.

So, for example my new database is set up like so:

News_ID  |  User_ID  |                 News_Desc            |   Timestamp

  2643         904     {User904} and {User890} commented on     SomeTimestamp
                       {User222}'s new {Album724}.

Anything inside {'s represents data that would be serialized using JSON.

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

Will it be difficult to separate the serialized data from the rest of the string using regular expressions?

Answer 1

What happens if User890 deletes his/her comment? I think you need to be more atomic - possibly storing the type of action (comment) with the actioner (User890), then generate the actual story on the fly, with heavy caching. This would also help the issue of translation, if you extend your site to several markets/audiences.