Facebook Database Design

Database Schema for Individual and Group accounts(Like Facebook Users and Pages)

You should use different tables for different entities.

1. Users
2. Groups
3. Moderators (contains id of corresponding users and moderated by them groups id)
4. Followers (contains id of users and followed by them groups)

Tables 3 and 4 provides such called "many-to-many" relationship (multiple users can follow multiple groups), as databases support only one-to-many or one-to-one relationship from the box

database design post privacy

You could add table like "post_access" with two columns post_id and user_id.

There are some possible tricks to prevent fast growing of this table:

1. Author of the post could see it without adding of record to post_access
2. Post table could have flag field "ispublic", and this post is visible to every one
3. You could create user groups (additional table) and use post access not for users, but for groups (this make sense if you expect some grouping of users)

Facebook like data structure

If you want to represent this sort of structure in a relational database, then you need to use a hierarchy normally referred to as table inheritance. In table inheritance, you have a single table that defines a parent type, then child tables whose primary keys are also foreign keys back to the parent.

Using the Facebook example, you might have something like this:

User
------------
UserId (PK)

Item
-------------
ItemId (PK)
ItemType (discriminator column)
OwnerId (FK to User)

Status
------------
ItemId (PK, FK to Item)
StatusText 

RelationshipUpdate
------------------
ItemId (PK, FK to Item)
RelationshipStatus
RelationTo (FK to User)

Like
------------
OwnerId (FK to User)
ItemId (FK to Item)
Compound PK of OwnerId, ItemId

In the interest completeness, it's worth noting that Facebook doesn't use an RDBMS for this sort of thing. They have opted for a NoSQL solution for this sort of storage. However, this is one way of storing such loosely-coupled information within an RDBMS.

Database Design for a system that has Facebook like groups

I've got an idea, which is a workaround basically: have another table like: group_type in which you have id(the PK) and then you have tablename (the full table name of the type).

Then, you should have a FK from your Group table linking to this group_type table.

id    tablename
--------------------
1     School Group
2     Interest Group

After all this is done, you could build your queries based on the values from this table, as an example:

JOIN (SELECT tablename FROM group_type WHERE id=group.group_type_id) ON ..

---

Related Topics