We are along the way of creating a normal enterprise social media platform in ASP.Internet MVC. Among the key options that come with any social media website would be the social objects which are published by customers either clearly (text updates, photos, blogs, videos etc) or unconditionally ('user is attending event', 'user has up-to-date page' etc).

They are all basically fairly similar - i.e. they all are shown on users' activity streams, around the group pages that they're published to, around the user profiles of customers who published them and strained in similar ways - e.g. "show me exactly what happened within the last seven days for group X using the tag Y".

You want to define a couple of core publish types (blog, text update, event attendance, page edited etc) but to be capable of easily extend to ensure that merchants and clients from the software can also add their very own types (e.g. news-article) using their own custom metadata and fields (that ought to be searchable/filterable). Think about these because the "social" same as Sharepoint lists!

Anyway the question I've is: What's the best data structure to do this when it comes to performance, scalability and easy extensibility?

This is exactly what I am presently thinking (pseudo code / database structure):

public class SocialObject
{
 int Id;
 DateTime Date;
 string Url;
 string Title;
 string Text;

 Media[] Attachments; //photos, videos, links etc

 int OwnerId; //user who posted it
 int GroupId; //group it was posted to
 int PageId; //page it was posted to
 int PostTypeId;
 int? SourceId; //source - e.g. desktop client, email, web

 Like[] Likes;
 Comment[] Comments;
 Repost[] Reposts;

 Tag[] Tags;
 Mention[] Mentions; //user IDs mentioned in this post
 Metadata[] MetadataValues;
}

public class Metadata
{
 int SocialObjectId;
 int MetadataTypeId;
 int? MetadataValueId; //for metadata types with list values - for filtering
 string Value;
}

(All arrays reference separate tables within the DB)

Is the best way to get it done? - i.e. store all social objects within the same table (and permit stretching through the metadata table for further fields/info) or shall we be held smoking something I should not be? Keep in mind this table will probably become quite large - 100,000s to countless rows.

cheers, Marcus

This post with a Facebook engineer should provide you with a concept of exactly what the major issues are if this involves scaling. Mostly you have to consider how rapidly are you able to generate/serve a listing of posts produced by confirmed user's buddies.

Also found this video presentation from Bebo comparable problem: http://ecn.channel9.msdn.com/o9/mix/10/mp4/EX04.mp4