在一个表中定义多个外键到多个表 [英] Defining multiple foreign keys in one table to many tables

问题描述

我有 3 个模型:

发帖:

• 身份证
• 标题
• 正文

照片:

• 身份证
• 文件路径

评论:

• 身份证
• post_id
• 正文

和数据库中的相应表.现在，如果我只想对我的帖子发表评论，我可以简单地添加以下外键:ALTER TABLE comment ADD FOREIGN KEY (post_id) REFERENCES post (id).但我想对其他模型(照片、个人资料、视频等)发表评论，并将所有评论保存在 一个 表中.在这种情况下，我该如何定义外键(ORM 肯定需要 FK)?

and corresponding tables in DB. Now, if I want to have comments only for my posts I can simply add following foreign key: ALTER TABLE comment ADD FOREIGN KEY (post_id) REFERENCES post (id). But I want to have comments for other models (photo, profile, video, etc) and keep all comments in one table. How can I define foreign keys (i definitely need FKs for ORM) in such case?

推荐答案

你可以这样做:

 post:
  * post_id (PK)
  * title
  * body

 photo:
  * photo_id (PK)
  * filepath

 comment:
  * comment_id (PK)
  * body

 comment_to_post
  * comment_id (PK) -> FK to comment.comment_id
  * post_id (PK) -> FK to post.post_id

 comment_to_photo
  * comment_id (PK) -> FK to comment.comment_id
  * photo_id (PK) -> FK to photo.photo_id

仍然有可能拥有属于两个不同项目的评论.如果您认为这是一个问题，我可以尝试改进设计.

There's still the possibility of having a comment that belongs to two different items. If you think that would be an issue I can try to improve the design.

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