在数据库中实现评论和喜欢 [英] Implementing Comments and Likes in database

问题描述

我是一名软件开发人员。我喜欢代码，但我讨厌数据库...目前，我正在创建一个网站，用户将允许用户将实体标记为喜欢（如在FB中），标记 。

我在数据库表设计上遇到了困难。解决方案是微不足道的，如果我们可以做到这一点只有一种类型的东西（例如照片）。但我需要为5个不同的事情启用这个（现在，但我也假设这个数字可以增长，因为整个服务增长）。

我发现一些类似问题是，如何正确地，有效地

和弹性地设计数据库，以便它可以存储对于不同表的表，和标签。一些设计模式作为答案将是最好的;）

详细描述：
我有一个表 用户有一些用户数据，以及另外3个表格：照片 ，与 文章 em>。我要启用任何已登录的用户：

• 对这3个表中的任何一个进行评论

  b $ b

• 将任何人标记为喜欢




• $ b

• 我还想计算每个元素的喜欢次数和特定标签的使用次数。
  
  
  
  

1 ^st 方法：： c> Tag [TagId，tagName，tagCounter] ，那么我将创建多对多关系表 ： Photo_has_tags ， Place_has_tag ， Article_has_tag 。

b）注释的计数相同。

c）我将创建一个表 LikedPhotos [ idUser，idPhoto] ， LikedArticles [idUser，idArticle] ， LikedPlace [idUser，idPlace] 。 喜欢次数将通过查询计算（我认为不好）。和...

我真的不喜欢这个设计的最后一部分，它闻起来不好的我;）

2 ^和方法：

ElementType [idType，TypeName == some table name] 其将由管理员（me）填入表的名称 em>已，已评论或已标记。然后我将创建表：

：

a） LikedElement [idLike，idUser，idElementType，idLikedElement] $ c>，并且对于Comments和Tags，每个都有相应的列。现在，当我想制作照片时，我将插入：

  typeId = SELECT id FROM ElementType WHERE TypeName =='Photo '
 INSERT（user id，typeId，photoId）
  

>



  typeId = SELECT id FROM ElementType WHERE TypeName =='Place'
 INSERT（user id，typeId，placeId）
  

等等...我认为第二种方法更好，但我也觉得有些东西缺少这个设计以及...

最后，我也想知道哪个最好的地方存储计数器多少次的元素是喜欢的。我只能想到两种方式：

1. 在元素（照片/文章/地方）


$ b

我希望我的解释

$ p


解决方案

最可扩展的解决方案是只有一个基表 ，标签和注释），并从其继承所有其他表。添加一种新的实体只需添加一个新的继承表，然后自动插入整个like / tag /注释机制。





实体关系术语这是类别（请参阅​​ ERwin方法指南，部分：子类型关系）。类别符号是：








假设用户可以喜欢多个实体，同一个标签可以用于多个实体，但注释是实体特定的，您的模型可能如下所示：








---





BTW，大约有3种方法来实现ER类别：





<

• 所有具体类型和抽象类型在单独的表中。






除非你有非常严格的性能要求，第三种方法可能是最好的（意思是物理表匹配1：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.
  
  

1^st 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 ;)


2^nd 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.

解决方案

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, section: "Subtype Relationships"). The category symbol is:

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:

---

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).

这篇关于在数据库中实现评论和喜欢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！