We're in the process of building a generic enterprise social networking platform in ASP.NET MVC. One of the key features of any social networking website are the social objects that are posted by users either explicitly (text updates, photos, blogs, videos etc) or implicitly ('user is attending event', 'user has updated page' etc).
These are all essentially fairly similar - i.e. they are all displayed on users' activity streams, on the group pages that they are posted to, on the user profiles of users who posted them and filtered in similar ways - e.g. "show me everything that happened in the last 7 days for group X with the tag Y".
We want to define a few core post types (blog, text update, event attendance, page edited etc) but to have the ability to easily extend so that resellers and clients of the software can add their own types (e.g. news-article) with their own custom metadata and fields (which should be searchable/filterable). Think of these as the "social" equivalent of Sharepoint lists!
Anyway the question I have is: What is the best data structure to achieve this in terms of performance, scalability and ease of extensibility?
This is what I'm currently 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 in the DB)
Is this a sensible way to do it? - i.e. store all social objects in the same table (and allow extending via the metadata table for additional fields/info) or am I smoking something I shouldn't be? Bear in mind that this table is likely to become quite large - 100,000s to millions of rows.
cheers, Marcus