多个表需要一对多关系 [英] multiple tables need one to many relationship

问题描述

我有一个具有多个表的SQL数据库：A，B，C，D.这些表中的实体是完全不同的东西，不同的列以及它们之间不同的关系。

但是所有分享一个小事情：在这种情况下需要一个评论系统将具有相同的结构：author_id，日期，内容等。

我不知道哪个策略会是最好的这个模式有A，.. D表使用注释系统。在一个古典的博客网站中，我将在comments表中使用一个与一个post_id的一对多关系。

在这看来，我需要一个A_comments，B_comments等表来处理这个问题，看起来有点奇怪。

有更好的方法吗？

解决方案

创建一个 comment 表格，一个 comment_id 主键和评论的各种属性。

此外，创建 A_comment 因此：

  CREATE TABLE A_comment （
 comment_id PRIMARY KEY REFERENCES comment（comment_id），
 A_id REFERENCES A（A_id）
）
  

同样为B，C和D.这样可以确保 comment 和所有其他表之间的引用完整性，如果你不能直接在评论中将ids存储到A，B，C和D。

声明 A_comment.comment_id 作为主键确保注释只能属于A中的一个条目。它不会阻止注释属于A中的条目和B中的条目，但只有这样你可以用外键实现;这将需要数据库级限制，我不知道数据库的支持。

此设计也不会阻止孤立的评论，但我不能想到任何方式为了在SQL中防止这种情况，除了当然要做你想避免的事情：创建多个注释表。

I have a SQL database with multiple tables: A, B, C, D. Entities in those tables are quite different things, with different columns, and different kind of relations between them.
However they all share one little thing: the need for a comment system wich in this case would have an identical structure: author_id, date, content, etc.

I wonder which strategy would be the best for this schema to have A,..D tables use a comment system. In a classical 'blog' web site I would use a one-to-many relationship with a post_id inside the 'comments' table.

Here it looks like I need a A_comments, B_comments, etc tables to handle this problem, which looks a little bit weird.

Is there a better way ?

解决方案

Create a comment table with a comment_id primary key and the various attributes of a comment.

Additionally, create A_comment thus:

CREATE TABLE A_comment (
    comment_id PRIMARY KEY REFERENCES comment(comment_id),
    A_id REFERENCES A(A_id)
)

Do likewise for B, C and D. This ensures referential integrity between comment and all the other tables, which you can't do if you store the ids to A, B, C and D directly in comment.

Declaring A_comment.comment_id as the primary key ensures that a comment can only belong to one entry in A. It doesn't prevent a comment from belonging to an entry in A and an entry in B, but there's only so much you can achieve with foreign keys; this would require database-level constraints, which no database I know of supports.

This design also doesn't prevent orphaned comments, but I can't think of any way to prevent this in SQL, except, of course, to do the very thing you wanted to avoid: create multiple comment tables.

这篇关于多个表需要一对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！