一张桌子与多张桌子

问题描述

我有以下表格：
- 帖子
- 文件
- 事件
- 文件

每篇文章文件，事件，文件可以有意见。

这个更好的数据库方案是什么，为什么？

第一个解决方案

• comments table（comment_id，author_id，comment） / li>
  
• 创建关系的4个表（posts_comments（post_id，comment_id），files_comments（file_id，comment_id），events_comments（event_id，comment_id），documents_comments（document_id，comment_id））

第二个解决方案

• （comment_id，author_id，comment）


• items_comments（comment_id，parent_type（enum ['post'，'file'，'event'，'document']），parent_id）

这是一个更好的解决方案，或者我应该使用哪一个？

解决方案

可能有真正的原因需要/需要单个注释表。例如，这将使查看给定用户的所有评论变得更简单。此外，搜索所有评论将更简单（在一个表上放置一个FTS索引，您已完成）。

另一方面，如果没有令人信服的理由将注释保留在单个表中，则可能有第三个（而且相当明显）的解决方案。

为每个项目（帖子，事件，文件，文档）创建一个单独的注释表。在这种情况下，RI关系将非常简单的定义和描述。此外，如果您经常打字特别查询，可能会使其更简单。例如

  select * from documents d left join doc_comments c 
 on d.id = c.docid 
其中d.id = 42; 
  

这些对您的情况都不是相关或重要的，但可能值得考虑。一个额外的随机思想：OP中的两个解决方案都有感觉，他们定义了多对多关系（例如，一个注释可以属于多个项目） 。假设这不是所期望的情况，可以用适当的唯一索引来防止...但是...仍然具有初始的外观，似乎可能导致可能的混淆。

I have the following tables:- posts- files- events- documents

Every post, file, event, document can have comments.

What is the better database scheme for this, and why?

First solution

• comments table (comment_id, author_id, comment)
• 4 tables to create relations (posts_comments(post_id, comment_id), files_comments(file_id, comment_id), events_comments(event_id, comment_id), documents_comments(document_id, comment_id))

Second solution

• comments table (comment_id, author_id, comment)
• items_comments (comment_id, parent_type (enum['post', 'file', 'event', 'document']), parent_id)

What is a better solution for this, or which one of two should I use?

解决方案

There may be real reasons for wanting/needing a single comments table. For example, it would make it simpler to view all comments from a given user. Also, searches through all comments would be simpler (put one FTS index on the one table and you are done).

On the other hand, if there is not a compelling reason to keep the comments in a single table, there is a possible third (and rather obvious) solution.

Create a separate comments table for each item (post, event, file, document). The RI relationships would be very simple to define and describe in that situation. Also, if you are typing ad-hoc queries very often, it could make it simpler. For example

 select * from documents d left join doc_comments c
                           on d.id = c.docid
                           where d.id=42;

None of this may be pertinent or important to your situation, but it could be worth considering.

One additional random thought: Both solutions in the OP have the "feel" that they are defining a many-to-many relationship (e.g., a comment can belong to multiple items). Assuming that is not the desired situation, it can be prevented with the appropriate unique index, ... but still ... it has that initial appearance, which seems as if it could lead to possible confusion.

这篇关于一张桌子与多张桌子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！