Facebook Database Design Loves It

Question

New to database design, and I was wondering how to effectively create something like Facebook, taking into account future scalability.

Let's say you have 3 tables: users, photos and albums. Say a user may like either a photograph or an album.

Should I use 1 table for both types of likes? This would probably mean that the user would have user_id, like_type (0-photo, 1 album, etc.), for example_value (id value of any content, be it photo_id or album_id)?

or do you have 2 different tables for each one you like (e.g. photos_likes and albums_likes)? which will only contain user_id and photo / album_id

I want the database design to be clean and semi-scaled, whether we will add many more objects in the future (videos, comments, notes, etc.) or tons of favorites.

Thanks!

Answer 1

You can try the inherited approach to the table, see the implementation of table inheritance for more details.

But in essence, it works the same as inheritance in code, you have a base table “Like”, and then tables that “inherit” from it “CommentLike”, “PhotoLike”, etc.

See attached chart for quick layout.

Answer 2

Two different tables. Thus, if you ever have an object that you want to add, like later, you can simply create a new table "object_likes" and save your favorite there.

If you want to save them all in one table, you will need a type table in which all types of objects will be stored, and in a similar table you will need to refer to type_id. This will allow you to add types later.

For me, the first method is much better.