CODEWITHSUNDEEP
database-designsocial-networking
daniel
daniel

Reputation: 1

Object table schema in social network

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.

Upvotes: 0

Views: 756

Answers (1)

RC_Cleland
RC_Cleland

Reputation: 2304

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.

Upvotes: 1

Related Questions