sql - 我如何设计这两个表以防止损坏数据？

Question

我有一个 comments 表，一条评论引用一个答案或一个问题作为外键:

id primary key not null,
question_id  references questions(id),
answer_id references answers(id),

将填充 answer_id 或 question_id，另一个将为空。

现在另一个表是notifications，每当创建评论时，我都会创建一个通知:

id primary key not null,
comment_id references comments(id),
question_id references questions(id),
answer_id references answers(id),

将填充 question_id 或 answer_id，但我想确保它与为该 comment 记录填充的内容一致。

如何确保通知将引用评论本身引用的相同资源？换句话说，如果我有一个引用问题的评论，然后我想为该评论创建一个通知但让它引用一个答案，数据库不应该允许这样做。我该如何施加这样的约束？

Answer 1

您应该规范化表格，并使用从通知到评论的引用来查找 question_id 和 answer_id。

create table comments (
  id serial primary key,
  question_id int references questions(id),
  answer_id int references answers(id)
);

create table notifications (
  id primary key not null,
  comment_id int references comments(id)
);

使用联接访问日期:

select question.*
from notifications
join comments on comments_id = comments.id
join questions on question_id = questions.id;