MySQL中良好的一对多关系实践 [英] Good one-to-many relation practice in MySQL

问题描述

我正在开发MySQL数据库的结构，但是在结构上遇到了一个小的决策问题.

I am developing the structure of the MySQL database and I've faced a small decisional problem about its structure.

我有2张桌子:

1. 网站上发布的所有消息.
2. 网站上发布的所有评论.

每条消息都可以有多个相关的评论.在消息和与之相关的评论之间建立联系的更好的方法是什么?

Every message can have more than one comment associated to it. What is a better way to make connection between a message and comments related to it?

1. 有一个用于注释的字段，其中包含相关消息的ID.
2. 对于包含以json格式的相关注释ID组成的数组的消息，有一个字段.

我认为通常使用第一种方法，然后使用MySQL查询来查找具有相应消息的message_id的注释.但是，当有成千上万的评论时，效率如何?

I think that usually the first method is used and then MySQL query is used to find comments that have message_id of corresponding message. But how efficient will it be when there are hundreds of thousands of comments?

在这种情况下，解码json字符串并通过确切的唯一ID访问注释会更高效，更快捷吗?

Will in this case decoding json string and accessing comments by exact unique id be more efficient and fast?

如果重要的话，我正在使用python作为后端.

I am using python for back-end if that matters.

推荐答案

您绝对应该在评论表中添加消息ID字段.在该字段上添加索引，不会有性能问题.

You should definitely add a message ID field to the comments table. Add an index on that field, and there will be no performance problems.

此解决方案不会破坏数据库规范化，它是合乎逻辑的且易于维护.例如，将数组存储在单个字段 中会破坏规范化，这将导致多个问题.

This solution does not break database normalization, it is logical and easy to maintain. For example, storing arrays in a single field will break normalization, which will lead to multiple problems.

这篇关于MySQL中良好的一对多关系实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！