Menu
I have 3 models:
Post:
Photo:
Comment:
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?
292
A table can have multiple foreign keys based on the requirement.
These foreign key fields are populated with data as records in the join table are created from either table it joins. A typical example of a many-to many relationship is one between students and classes. A student can register for many classes, and a class can include many students.
The short answer is no, a table is not allowed to contain multiple primary keys , as that goes against the fundamental principles of relational database design (see: [database normalisation](https://en.wikipedia.org/wiki/Database_normalisation) and [Third normal form](https://en.wikipedia.org/wiki/Third_normal_form) ).
In a word, yes. You can have as many foreign keys as you want referencing the same primary key.
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.
82
Find something common to post, profile, etc -- I have used Entity for a lack of better word, then subtype.
24
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
