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

Implementing Comments and Likes in database

提问by Kokos

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), tagit and comment.

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

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

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

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, efficientlyand elasticallydesign the database, so that it can store comments for different tables, likesfor different tablesand tagsfor them. Some design pattern as answer will be best ;)

现在的问题是，如何正确，有效和弹性设计数据库，以便它可以存储留言给不同的表，喜欢不同的表和标签他们。一些设计模式作为答案将是最好的;)

Detailed description: I have a tableUserwith some user data, and 3 more tables: Photowith photographs, Articleswith articles, Placeswith places. I want to enable any logged user to:

详细描述：我有一个表User与一些用户数据，以及3个表：Photo用照片，Articles用文章，Places用的地方。我想让任何登录的用户能够：

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

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

• 将其中任何一个标记为喜欢

• 用一些标签标记其中任何一个

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

1^stapproach:

1^日的做法：

a) For tags, I will create a tableTag [TagId, tagName, tagCounter], then I will create many-to-manyrelationships tablesfor: Photo_has_tags, Place_has_tag, Article_has_tag.

a) 对于标签，我将创建一个表Tag [TagId, tagName, tagCounter]，然后我将创建多对多关系表：Photo_has_tags, Place_has_tag, Article_has_tag。

b) The same counts for comments.

b) 评论同样重要。

c) I will create a tableLikedPhotos [idUser, idPhoto], LikedArticles[idUser, idArticle], LikedPlace [idUser, idPlace]. Number of likeswill be calculated by queries(which, I assume is bad). And...

c) 我将创建一个表LikedPhotos [idUser, idPhoto], LikedArticles[idUser, idArticle], LikedPlace [idUser, idPlace]。喜欢的数量将通过查询来计算（我认为这很糟糕）。和...

I really don't like this design for the last part, it smells badly for me ;)

我真的不喜欢最后一部分的这个设计，它对我来说闻起来很糟糕；)

2^ndapproach:

2^次的方法：

I will create a table ElementType [idType, TypeName == some table name]which will be populated by the administrator (me) with the names of tablesthat can be liked, commentedor tagged. Then I will create tables:

我将创建一个表ElementType [idType, TypeName == some table name]，该表将由管理员（我）填充，其中包含可以被喜欢、评论或标记的表的名称。然后我将创建表：

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:

a)LikedElement [idLike, idUser, idElementType, idLikedElement]和 Comments 和 Tags 相同，每个都有适当的列。现在，当我想制作一张喜欢的照片时，我会插入：

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

1. 在元素 ( Photo/Article/Place) 表中
2. 通过选择计数（）。

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

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

回答by Branko Dimitrijevic

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.

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

Entity-relationship term for this is "category" (see the ERwin Methods Guide, section: "Subtype Relationships"). The category symbol is:

实体关系术语是“类别”（参见 ERwin 方法指南，部分：“子类型关系”）。类别符号为：

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":

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

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

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

回答by wallyk

Since you "hate" databases, why are you trying to implement one? Instead, solicit help from someone who loves and breathes this stuff.

既然你“讨厌”数据库，你为什么要尝试实现一个？相反，向喜欢和呼吸这些东西的人寻求帮助。

Otherwise, learn to love your database. A well designed database simplifies programming, engineering the site, and smooths its continuing operation. Even an experienced d/b designer will not have complete and perfect foresight: some schema changes down the road will be needed as usage patterns emerge or requirements change.

否则，请学会热爱您的数据库。精心设计的数据库可简化站点的编程和工程设计，并使其持续运行更加顺畅。即使是经验丰富的 d/b 设计师也不会有完整和完美的远见：随着使用模式的出现或需求的变化，需要在未来进行一些架构更改。

If this is a one man project, program the database interface into simple operations using stored procedures: add_user, update_user, add_comment, add_like, upload_photo, list_comments, etc. Do not embed the schema into even one line of code. In this manner, the database schema can be changed without affecting any code: only the stored procedures should know about the schema.

如果这是一个单人项目，请使用存储过程将数据库接口编程为简单的操作：add_user、update_user、add_comment、add_like、upload_photo、list_comments 等。不要将模式嵌入到一行代码中。通过这种方式，可以在不影响任何代码的情况下更改数据库模式：只有存储过程应该知道模式。

You may have to refactor the schema several times. This is normal. Don't worry about getting it perfect the first time. Just make it functional enough to prototype an initial design. If you have the luxury of time, use it some, and then delete the schema and do it again. It is alwaysbetter the second time.

您可能需要多次重构架构。这是正常的。不要担心第一次就完美。只需使其功能足以对初始设计进行原型设计即可。如果您有足够的时间，请使用它，然后删除架构并再次执行。它总是更好的第二次。

回答by user964260

This is a general idea please don′t pay much attention to the field names styling, but more to the relation and structure

这是一个总体思路，请不要太注意字段名称的样式，而要注意关系和结构

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"

此伪代码将获取 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)

此伪代码将获取所有喜欢或喜欢 ID 5 照片的用户
（您可以使用 count() 来获取喜欢的数量）

SELECT * FROM actions  
WHERE actions.id_Stuff = 5  
AND actions.typeStuff="photo"  
AND actions.typeAction = "like"  

回答by nobody

Definitely go with the second approach where you have one table and store the element type for each row, it will give you a lot more flexibility. Basically when something can logically be done with fewer tables it is almost always better to go with fewer tables. One advantage that comes to my mind right now about your particular case, consider you want to delete all liked elements of a certain user, with your first approach you need to issue one query for each element type but with the second approach it can be done with only one query or consider when you want to add a new element type, with the first approach it involves creating a new table for each new type but with the second approach you shouldn't do anything...

绝对使用第二种方法，即您有一个表并存储每一行​​的元素类型，它将为您提供更大的灵活性。基本上，当逻辑上可以用更少的表完成某件事时，使用更少的表几乎总是更好。关于您的特定情况，我现在想到的一个优势是，考虑您要删除某个用户的所有喜欢的元素，使用第一种方法，您需要为每种元素类型发出一个查询，但使用第二种方法可以完成只有一个查询或考虑何时添加新元素类型，第一种方法涉及为每个新类型创建一个新表，但第二种方法你不应该做任何事情......

回答by erencan

as far as i understand. several tables are required. There is a many to many relation between them.

据我所理解。需要几张表。它们之间存在多对多关系。

• Table which stores the user data such as name, surname, birth date with a identity field.
• Table which stores data types. these types may be photos, shares, links. each type must has a unique table. therefore, there is a relation between their individual tables and this table.
• each different data type has its table. for example, status updates, photos, links.
• the last table is for many to many relation storing an id, user id, data type and data id.

• 存储用户数据（如姓名、姓氏、出生日期和身份字段）的表。
• 存储数据类型的表。这些类型可能是照片、共享、链接。每种类型都必须有一个唯一的表。因此，它们的各个表与该表之间存在关系。
• 每种不同的数据类型都有其表。例如，状态更新、照片、链接。
• 最后一个表是用于存储 id、用户 id、数据类型和数据 id 的多对多关系。

回答by djna

Look at the access patterns you are going to need. Do any of them seem to made particularly difficult or inefficient my one design choice or the other?

查看您将需要的访问模式。它们中的任何一个似乎使我的一种设计选择或另一种设计变得特别困难或效率低下？

If not favour the one that requires the fewer tables

如果不喜欢需要较少桌子的那个

In this case:

在这种情况下：

1. Add Comment: you either pick a particular many/many table or insert into a common table with a known specific identifier for what is being liked, I think client code will be slightly simpler in your second case.
2. Find comments for item: here it seems using a common table is slightly easier - we just have a single query parameterised by type of entity
3. Find comments by a person about one kind of thing: simple query in either case
4. Find all comments by a person about all things: this seems little gnarly either way.

1. 添加评论：您要么选择一个特定的多/多表，要么插入一个带有已知特定标识符的公用表，我认为在您的第二种情况下，客户端代码会稍微简单一些。
2. 查找 item 的评论：这里似乎使用公共表稍微容易一些 - 我们只有一个按实体类型参数化的查询
3. 查找某人对某件事的评论：在任何一种情况下都可以进行简单查询
4. 查找一个人对所有事情的所有评论：无论哪种方式，这似乎都有些粗糙。

I think your "discriminated" approach, option 2, yields simpler queries in some cases and doesn't seem much worse in the others so I'd go with it.

我认为您的“区分”方法，选项 2，在某些情况下会产生更简单的查询，而在其他情况下似乎不会更糟，所以我会采用它。

回答by Oroboros102

Consider using table per entity for comments and etc. More tables - better sharding and scaling. It's not a problem to control many similar tables for all frameworks I know.

考虑使用每个实体的表进行评论等。更多的表 - 更好的分片和缩放。为我知道的所有框架控制许多类似的表不是问题。

One day you'll need to optimize reads from such structure. You can easily create agragating tables over base ones and lose a bit on writes.

有一天，您将需要优化此类结构的读取。您可以轻松地在基本表上创建聚合表，并在写入时丢失一点。

One big table with dictionary may become uncontrollable one day.

一张放着字典的大桌子可能有一天会变得无法控制。