StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POImplementing Comments and Likes in database
text
Body
copied!<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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload