Alex Rivera | Logout

Implementing Comments and Likes in database

Asked 2011-11-13T16:11:08.243
190

I'm a software developer. I love to code, but I hate databases... Currently, I'm creating a website on which a user will be allowed to mark an entity as liked (like in FB), tag it and comment.

I get stuck on database tables design for handling this functionality. Solution is trivial, if we can do this only for one type of thing (eg. photos). But I need to enable this for 5 different things (for now, but I also assume that this number can grow, as the whole service grows).

I found some similar questions here, but none of them have a satisfying answer, so I'm asking this question again.

The question is, how to properly, efficiently and elastically design the database, so that it can store comments for different tables, likes for different tables and tags for them. Some design pattern as answer will be best ;)

Detailed description: I have a table User with some user data, and 3 more tables: Photo with photographs, Articles with articles, Places with places. I want to enable any logged user to:

  • comment on any of those 3 tables

  • mark any of them as liked

  • tag any of them with some tag

  • I also want to count the number of likes for every element and the number of times that particular tag was used.

1st approach:

a) For tags, I will create a table Tag [TagId, tagName, tagCounter], then I will create many-to-many relationships tables for: Photo_has_tags, Place_has_tag, Article_has_tag.

b) The same counts for comments.

c) I will create a table LikedPhotos [idUser, idPhoto], LikedArticles[idUser,

Edit
Report

1 Answer

4

Consider using table per entity for comments and etc. More tables - better sharding and scaling. It's not a problem to control many similar tables for all frameworks I know.

One day you'll need to optimize reads from such structure. You can easily create agragating tables over base ones and lose a bit on writes.

One big table with dictionary may become uncontrollable one day.

answered 2011-11-13T17:01:41.150

Your Answer