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

问题描述

我有一个包含多个表的 SQL 数据库:A、B、C、D.这些表中的实体是完全不同的东西，有不同的列，它们之间的关系也不同.
然而，它们都有一个共同点:需要一个评论系统，在这种情况下，它具有相同的结构:作者 ID、日期、内容等.

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 which, in this case, would have an identical structure: author_id, date, content, etc.

我想知道哪种策略最适合让 A,..D 表使用注释系统的模式.在经典的博客"网站中，我会在评论"表中使用带有 post_id 的一对多关系.

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.

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

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

有更好的方法吗?

推荐答案

使用 comment_id 主键和评论的各种属性创建一个 comment 表.

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

另外，创建 A_comment 如下:

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

对 B、C 和 D 执行同样的操作.这确保了 comment 和所有其他表之间的参照完整性，如果将 id 存储到 A、B、C 和 D，则无法做到这一点直接在comment.

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.

将A_comment.comment_id 声明为主键可确保评论只能属于A 中的一个条目.它不会阻止评论属于A 中的一个条目和B 中的一个条目，但你只能用外键实现这么多；这将需要数据库级别的约束，我所知道的数据库都不支持.

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.

这种设计也不会阻止孤立评论，但我想不出任何方法可以在 SQL 中阻止这种情况，当然，除了做你想避免的事情:创建多个评论表.

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屋！