> For the complete documentation index, see [llms.txt](https://devs.gitbook.io/0/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://devs.gitbook.io/0/solution-sql-database-comments-and-likes-in-database.md). # Solution SQL Database Comments and Likes in database Stackoverflow ở đây: https\://stackoverflow\.com/questions/8112831/implementing-comments-and-likes-in-database ## Problem and solution 1 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, idArticle]`, `LikedPlace [idUser, idPlace]`. Number of *likes* will be calculated by *queries* (which, I assume is bad). And... I really don't like this design for the last part, it smells badly for me ;) \ **2nd approach**: I will create a table `ElementType [idType, TypeName == some table name]` which will be populated by the administrator (me) with the names of *tables* that can be *liked*, *commented* or *tagged*. Then I will create *tables*: a) `LikedElement [idLike, idUser, idElementType, idLikedElement]` 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: ``` typeId = SELECT id FROM ElementType WHERE TypeName == 'Photo' INSERT (user id, typeId, photoId) ``` and for places: ``` typeId = SELECT id FROM ElementType WHERE TypeName == 'Place' INSERT (user id, typeId, placeId) ``` and so on... I think that the second approach is better, but I also feel like something is missing in this design as well... 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: 1. in element (`Photo/Article/Place`) table 2. by select count(). I hope that my explanation of the issue is more thorough now. ## Cách hay nhất The most extensible solution is to have just one "base" table (connected to "likes", tags and comments), and "inherit" all other tables from it. Adding a new kind of entity involves just adding a new "inherited" table - it then automatically plugs into the whole like/tag/comment machinery. Entity-relationship term for this is "category" (see the [ERwin Methods Guide](https://support.ca.com/cadocs/0/e002961e.pdf), section: "Subtype Relationships"). The category symbol is: ![Category](https://i.stack.imgur.com/opRp8.png) Assuming a user can like multiple entities, a same tag can be used for more than one entity but a comment is entity-specific, your model could look like this: ![ER Diagram](https://i.stack.imgur.com/5lv3n.png) BTW, there are roughly 3 ways to implement the "ER category": * All types in one table. * All concrete types in separate tables. * All concrete and abstract types in separate tables. Unless you have very stringent performance requirements, the third approach is probably the best (meaning the physical tables match 1:1 the entities in the diagram above). ## Cách thiết kế One way table ![This is a general idea please don´t pay much attention to the field names styling, but more to the relation and structure](https://948989914-files.gitbook.io/~/files/v0/b/gitbook-legacy-files/o/assets%2F-LFzrH-4G2Cz8-7WTUgc%2F-LIylc4aMJ4YOSbBi3Kc%2F-LIylhgGUJIWC9EriUSl%2Fimage.png?alt=media\&token=53d9fdf6-575d-432d-a3c1-198e91eb1cd6) **This pseudocode will get all the comments of photo with ID 5**\ SELECT \* FROM actions\ WHERE actions.id\_Stuff = 5\ AND actions.typeStuff="photo"\ AND actions.typeAction = "comment" **This pseudocode will get all the likes or users who liked photo with ID 5**\ (you may use count() to just get the amount of likes) ``` SELECT * FROM actions WHERE actions.id_Stuff = 5 AND actions.typeStuff="photo" AND actions.typeAction = "like" ``` ## Tham khảo một vài bài viết khác