在数据库中实现评论和喜欢

2021-11-20 00:00:00 database design-patterns mysql database-design

我是一名软件开发人员.我喜欢编码，但我讨厌数据库......目前，我正在创建一个网站，允许用户将实体标记为喜欢(如在 FB 中)，标签 并评论.

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.

我在处理此功能的数据库表设计上陷入困境.如果我们只能为一种类型的事物(例如照片)执行此操作，则解决方案是微不足道的.但是我需要为 5 种不同的东西启用它(目前，但我也假设这个数字会随着整个服务的增长而增长).

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

详细说明:我有一个 table User 和一些用户数据，还有 3 个 tables:Photo with photographs、文章 与文章、地点 与地点.我想让任何登录的用户能够:

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:

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

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

第一种st方法:

1st approach:

a) 对于tags，我会创建一个table Tag [TagId, tagName, tagCounter]，然后我会创建多对多关系表用于:Photo_has_tags、Place_has_tag、Article_has_tag.

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) 评论的数量相同.

b) The same counts for comments.

c) 我将创建一个 table LikedPhotos [idUser, idPhoto], LikedArticles[idUser, idArticle], LikedPlace [idUser, idPlace].喜欢的数量将通过查询来计算(我认为这是不好的).还有……

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 方法:


2nd approach:

我将创建一个表 ElementType [idType, TypeName == some table name] 将由管理员 (me) 填充表的名称被喜欢、评论或标记.然后我将创建表:

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] 和评论和标签相同，每个都有适当的列.现在，当我想制作喜欢的照片时，我会插入:

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)

对于地点:

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. 在元素(照片/文章/地点)表中
  2. 通过选择计数().

我希望我现在对这个问题的解释更彻底.

I hope that my explanation of the issue is more thorough now.

推荐答案

最可扩展的解决方案是只有一个基"表(连接到喜欢"、标签和评论)，并从继承"所有其他表它.添加一种新的实体只需要添加一个新的继承"表 - 然后它会自动插入整个 like/tag/comment 机制.

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.

实体关系术语是类别"(参见 ERwin 方法指南，部分:子类型关系").类别符号为:

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:

顺便说一句，大致有 3 种方法可以实现ER 类别":

BTW, there are roughly 3 ways to implement the "ER category":

  • 一张表中的所有类型.
  • 单独表中的所有具体类型.
  • 单独表中的所有具体和抽象类型.

除非您有非常严格的性能要求，否则第三种方法可能是最好的(意味着物理表与上图中的实体 1:1 匹配).

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

相关文章