database design - Facebook "like" data structure

Question

I've been wondering how facebook manages the database design for all the different things that you can "like". If there is only one thing to like, this is simple, just a foreign key to what you like and a foreign key to who you are.

But there must be hundreds of different tables that you can "like" on facebook. How do they store the likes?

Answer 1

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.