社交网络中的对象表架构
我很难在我正在开发的社交网络中映射对象。我映射了对象列表，有 122 个主要对象。还有一个单独的系统对象列表，我现在暂时保留它。所以一个对象是：照片、视频、照片标签、视频标签、邮件附件、邮件等...
每个对象都有元数据，其中包括创建日期、所有者 ID、隐私级别、搜索级别等详细信息，对于文本项，有字数、它所属的类别、对其他人评论的引用等等。
现在根据我的理解，人们为每个对象提供了自己的表格。所以照片有它自己的表格，视频有它自己的表格，等等。但这意味着我将为我的对象拥有 122 个不同的表格。如果我将它们全部合并到一张表中，那么表扫描将成为一个问题，正如您所知，在社交网络中人们总是与对象交互，并且表会变得非常长。
我将对象详细信息分为两类：特定于对象（如照片）的字段将具有描述、大小、路径等列。然后是所有对象都具有的公共字段列表，如上所述（盗版级别、搜索级别等）。其中一些列是面向用户的，例如用户需要的隐私。有些纯粹用于系统后端报告目的，例如一周中的某一天、一月中的某一天、一年中的某一天等。
因此对此进行建模将是最好的方法。总结起来分为三层：
系统报告/分析的对象详细信息
所有对象共享的对象详细信息，例如创建日期、隐私等
对象类型特有的对象详细信息。
当然，如果我需要跨对象提取报告，我拥有的表越多，查询就越复杂。在顶部，我有一个对象查找表，其中包含对象的父级和子级列表，因此所有其他对象表将从那里流出。原因是我需要维护新闻源、通知等的默认文本列表，因此我计划将所有这些文本映射到对象查找表本身中。
I am having a hard time mapping objects in a social networking I am developing. I mapped the object list and I have 122 main objects. There is a separate list of system object too which I am keeping out for now. So an object is: Photo, Video, photo tag, video tag, mail attachment, mail, etc...
Each object has meta data which includes detail like create date, owner id, privacy level, search level, for text items there is a word count, category it belongs too, reference to comments by others, etc.
Now from my understanding people give each object its own table. So photos has it's own table, video has its own, etc. But this means i will have 122 different tables for my objects. If I combine them all into 1 table then table scans will be an issue as you know in a social network people are always interacting with objects and the table will grow super long.
I have broken the objects details into 2 categories: Fields specific to the object like photo will have a description, size, path, etc columns. Then there are a list of common fields which all objects have as mentioned above (the piracy level, search level etc). Some of these columns are user facing like privacy which users need. Some are purely for system backend reporting purposes such as day of week, day of month, day of year etc.
So to model this what would be the best approach. To sum up there are three layers:
Object details for system reporting/analytics
Object details hared by all objects like create date, privacy etc
Object details unique to the object type.
Of course the more tables I have the more complex queries will get esp if i need to pull reports across objects. At the top I have an object lookup table which has the parent and child list of objects so from there all other objects tables will flow out. Reason is I need to maintain a list of default text also for news feeds, notifications etc so I am planning to map all those in the object lookup table itself.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
丹尼尔，祝这个项目好运，这听起来很有挑战性。
对于数据库部分，我认为您不需要 122 个表，也不会最终只用一张表来完成整个项目。您的对象可以全部存储在一张表中。该表将包含一个使每一行都唯一的标识值、Hibernate 喜欢的版本号、最后更新的值、用作标志的八位列（例如：非活动、仅管理等）、对象类型号，以便您可以轻松获取单一类型的对象和一列 XML 数据。 XML 将保存对象的定义。 SQL Server 与 SQL2008 中的 XML 配合良好，并且还可以利用 XML 列上的索引。
您仍然需要许多其他表来支持用户、会员资格、订单、计费（您计划为此获得报酬）和报告。
Daniel, good luck on this project it sounds challenging.
For the database part I don't think you will need 122 tables nor will you end up with one table for the complete project. Your objects could all be stored in one table. The table would include an identity value which will make every row unique, a version number which Hibernate likes, a last updated value, eight bit columns to be used as flags (ex: inactive,admin only, etc ), an object type number so you can easily get just objects of a single type, and a column of XML data. The XML will hold the definition of your object. SQL server works well with XML in SQL2008 and can utilize indexes on the XML column also.
You will still need a number of other tables to support users, memberships, orders, billing ( you are planning to get paid for this) and reporting.