最有效的博客数据库设计(帖子和评论)。[英] Most efficient database design for a blog (posts and comments)

本文是小编为大家收集整理的关于最有效的博客数据库设计(帖子和评论)。的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

设计数据库存储博客文章和评论的最佳方法是什么?我目前正在思考一个帖子表,另一个用于评论,每个桌子都有一个帖子ID.

在我看来,在我的大量评论中拖曳以找到相关帖子的评论会很昂贵,并且每次加载博客文章时都会完成(也许有一定数量的缓存).

有更好的方法吗?

推荐答案

在我看来,在我的一大片评论

上拖了

所有数据库供应商都同意您.

他们提供"索引"来限制这一点.

其他推荐答案

您将使用的每个数据库系统都将使用索引.这意味着您的数据库系统并没有"浏览大桌子",而是保留了单独的评论列表以及与哪些帖子相关的帖子,就像书籍背面的索引一样.这使数据库系统可以非常快速地加载与帖子关联的注释,而且我对任何规模的博客的建议设计都没有任何问题.

索引通常用于将桌子与数百万行与数百万排的其他表格相关联 - 您必须拥有一个非常大的博客才能需要评论的符合,即使仍然,缓存可能会比贬值更好地为您服务.数据库.

您需要在评论表上定义索引,并将其与保存帖子ID的任何列将其关联.如何完成取决于您正在使用的数据库系统.

其他推荐答案

尝试这样的事情:

Blog
BlogID     int auto number PK
BlogName   string
...

BlogPost
BlogPostID   int auto number PK
BlogID       int FK to Blog.BlogID, index
BlogContent  string
....

Comment
CommentID       int auto number PK
BlogPostID      int FK to BlogPost.BlogPostID, index   
ReplyToCommentID int FK to Comment.CommentID  <<for comments on comments
...

本文地址:https://www.itbaoku.cn/post/597617.html

问题描述

What would be the best way of designing a database to store blog posts and comments? I am currently thinking one table for posts, and another for comments, each with a post ID.

It seems to me, however, trawling through a large table of comments to find those for the relevant post would be expensive, and would be done every time a blog post is loaded (perhaps with some amount of caching).

Is there a better way?

推荐答案

It seems to me, however, trawling through a large table of comments

All the database vendors agree with you.

They offer "indexes" to limit this.

其他推荐答案

Every database system you would be using to implement your blog will use indexing. What this means is that, rather than "trawling through a large table", your database system maintains a seperate list of comments and which posts they are associated with, much like the index at the back of a book. This allows the database system to load comments associated with a post extremely quickly, and I don't see any problems with your proposed design for a blog of any size.

Indexes are routinely used to associate tables with millions of rows with other tables with millions of rows - you would have to have an exceptionally large blog to require denormalization of comments, and even still, caching would probably serve you far better than denormalizing the database.

You will need to define an index on your comments table, and associate it with whatever column holds the Post ID. How that's done is dependent on what database system you are using.

其他推荐答案

try something like this:

Blog
BlogID     int auto number PK
BlogName   string
...

BlogPost
BlogPostID   int auto number PK
BlogID       int FK to Blog.BlogID, index
BlogContent  string
....

Comment
CommentID       int auto number PK
BlogPostID      int FK to BlogPost.BlogPostID, index   
ReplyToCommentID int FK to Comment.CommentID  <<for comments on comments
...