Defining multiple foreign keys in one table to many tables

Question

I have 3 models:

Post:

• id
• title
• body

Photo:

• id
• filepath

Comment:

• id
• post_id
• body

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?

Answer 1

You could do this:

 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.

Answer 2

Find something common to post, profile, etc -- I have used Entity for a lack of better word, then subtype.

• In this model one entity can have many comments, one comment belongs to one entity only.