Database Design for Facebook "likes"

Question

New to database design and I was wondering how to efficiently design something like Facebook likes with future scalability in mind.

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

Should I use 1 table for both types of likes? This would probably mean it would have an user_id, like_type(0-photo, 1-album etc), like_value(the id value of whatever content it is, whether it is photo_id or album_id)?

or have 2 different tables for each likes (ex. photos_likes and albums_likes)? which would only contain user_id and photo/album_id

I want to make sure that the database design is clean and semi-scaleproof whether we add many more objects in the future(videos, comments, notes, etc) or have a ton of likes.

Thanks!

Answer 1

You could try a inherited table approach see implementing table inheritence for more indepth detail.

But essentially it works just like inheritence in code, you have a base table 'Like' and then tables which 'inherit' from it 'CommentLike', 'PhotoLike' etc.

See attached diagram for a quick mockup.