社会对象 - 数据库结构，过滤，排序，可扩展性的最佳实践 [英] Social Objects - best practices for database structure, filtering, sorting, extensibility
问题描述
我们正在ASP.NET MVC中构建一个通用的企业社交网络平台。任何社交网络网站的主要特征之一是由用户明确地（文本更新，照片，博客，视频等）或隐含地（用户正在参加事件，用户已更新页面'等）。
这些都基本上相当类似 - 即它们都显示在用户的活动流上，在他们发布到的组页面上，在用户的用户配置文件发布他们并以类似的方式过滤 - 例如显示 7天发生在组X 中的所有内容和标记Y 。
我们想定义一些核心的职位类型（博客，文本更新，活动出席，页面编辑等），但有能力轻松扩展，以便软件的经销商和客户端可以添加自己的类型例如新闻文章）与他们自己的自定义元数据和字段（应该可搜索/可过滤）。将这些看作是Sharepoint列表的社交等同物！
无论如何，我的问题是：在性能，可扩展性方面，最好的数据结构是什么
<$>这是我目前的想法（伪代码/数据库结构）：
public class SocialObject
{
int Id;
DateTime Date;
string Url;
string标题;
string Text;
Media []附件; //照片，视频，链接等
int OwnerId; // user who posted it
int GroupId; // group it posted posted to
int PageId; // page被发布到
int PostTypeId;
int？ SourceId; // source - e.g.桌面客户端，电子邮件，web
喜欢[]喜欢;
评论[]评论;
Repost []
标签[] Tags;
提及[]提及; //此帖中提及的用户ID
元数据[] MetadataValues;
}
public class元数据
{
int SocialObjectId;
int MetadataTypeId;
int？ MetadataValueId; //对于带有列表值的元数据类型 - 用于过滤
string Value;
}
（所有数组引用数据库中的单独表）
这是一个明智的方法吗？ - 即将所有社交对象存储在同一个表中（并允许通过元数据表扩展其他字段/信息）或者我吸烟的东西，我不应该？记住这个表很可能变得相当大 - 100,000到数百万行。
干杯，
Marcus
这个post一个Facebook工程师应该给你一个想法的主要问题是什么时，扩展。大多数情况下，您需要考虑能够多快地生成/提供给定用户的朋友发布的所有帖子的列表。
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
This post by a Facebook engineer should give you an idea of what the major issues are when it comes to scaling. Mostly you need to consider how quickly can you generate/serve a list of all posts made by a given user's friends.
这篇关于社会对象 - 数据库结构，过滤，排序，可扩展性的最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
