title: Implementing Comments and Likes in database
body: <p>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 <em>liked</em> (like in FB), <em>tag</em> it and <em>comment</em>.</p>
  
  <p>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).</p>
  
  <p>I found some similar questions here, but none of them have a satisfying answer, so I'm asking this question again.</p>
  
  <p>The question is, how to properly, <em>efficiently</em> and <em>elastically</em> design the database, so that it can store comments for different <em>tables</em>, <em>likes</em> for different <em>tables</em> and <em>tags</em> for them. Some design pattern as answer will be best ;)</p>
  
  <p><strong>Detailed description</strong>:
  I have a <em>table</em> <code>User</code> with some user data, and 3 more <em>tables</em>: <code>Photo</code> with <em>photographs</em>, <code>Articles</code> with <em>articles</em>, <code>Places</code> with <em>places</em>. I want to enable any logged user to:</p>
  
  <ul>
  <li><p>comment on any of those 3 tables</p></li>
  <li><p>mark any of them as liked</p></li>
  <li><p>tag any of them with some tag</p></li>
  <li><p>I also want to count the number of likes for every element and the number of times that particular tag was used.
  <br/><br/></p></li>
  </ul>
  
  <p><strong>1<sup>st</sup> approach</strong>:</p>
  
  <p>a) For <em>tags</em>, I will create a <em>table</em> <code>Tag [TagId, tagName, tagCounter]</code>, then I will create <em>many-to-many</em> relationships <em>tables</em> for: <code>Photo_has_tags</code>, <code>Place_has_tag</code>, <code>Article_has_tag</code>.</p>
  
  <p>b) The same counts for comments.</p>
  
  <p>c) I will create a <em>table</em> <code>LikedPhotos [idUser, idPhoto]</code>, <code>LikedArticles[idUser, idArticle]</code>, <code>LikedPlace [idUser, idPlace]</code>. Number of <em>likes</em> will be calculated by <em>queries</em> (which, I assume is bad). And... </p>
  
  <p>I really don't like this design for the last part, it smells badly for me ;)</p>
  
  <p><br/>
  <strong>2<sup>nd</sup> approach</strong>:</p>
  
  <p>I will create a table <code>ElementType [idType, TypeName == some table name]</code> which will be populated by the administrator (me) with the names of <em>tables</em> that can be <em>liked</em>, <em>commented</em> or <em>tagged</em>. Then I will create <em>tables</em>:</p>
  
  <p>a) <code>LikedElement [idLike, idUser, idElementType, idLikedElement]</code> and the same for Comments and Tags with the proper columns for each. Now, when I want to make a photo liked I will insert:</p>
  
  <pre><code>typeId = SELECT id FROM ElementType WHERE TypeName == 'Photo'
  INSERT (user id, typeId, photoId)
  </code></pre>
  
  <p>and for places:</p>
  
  <pre><code>typeId = SELECT id FROM ElementType WHERE TypeName == 'Place'
  INSERT (user id, typeId, placeId)
  </code></pre>
  
  <p>and so on... I think that the second approach is better, but I also feel like something is missing in this design as well...</p>
  
  <p>At last, I also wonder which the best place to store counter for how many times the element was liked is. I can think of only two ways:</p>
  
  <ol>
  <li>in element (<code>Photo/Article/Place</code>) table</li>
  <li>by select count().</li>
  </ol>
  
  <p>I hope that my explanation of the issue is more thorough now.</p>