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?
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.
In that case you can add an ENUM field which will contain 'photo','profile'... It will be the second part of the foreign key
If you want to know if you can have multiple foreign keys to a single column then the answer is no you cant.
You can have separate foreign keys if you want to. So your can modify your comment table like this -
comment:
* comment_id (PK)
* PostID (FK to Post.PostID)
* PhotoID (FK to <Photo>.PhotoID)
* ProfileID (FK to <Profile>.ProfileID)
* Body
And, you will have to ensure that you allow nulls in PostID,PhotoID and ProfileID columns in Comment table and also perhaps set the default value to null.
Here is the DDL to achieve this -
Create table Photo
(
PhotoID int,
PhotoDesc varchar(10),
Primary key (PhotoID)
)
Create table Post
(
PostID int,
PostDesc varchar(10),
Primary key (PostID)
)
Create table Profiles
(
ProfileId int,
ProfileDesc varchar(10),
Primary key (ProfileId)
)
Create table Comment
(
CommentID int,
PhotoID int,
PostID int,
ProfileId int,
body varchar(10),
Primary key (CommentID),
Foreign key (PhotoID) references Photo(PhotoID),
Foreign key (PostID) references Post(PostID),
Foreign key (ProfileId) references Profiles(ProfileId)
)
insert into Photo values (1,'Photo1')
insert into Photo values (2,'Photo2')
insert into Photo values (3,'Photo3')
insert into Post values (11,'Post1')
insert into Post values (12,'Post2')
insert into Post values (13,'Post3')
insert into Profiles values (111,'Profiles1')
insert into Profiles values (112,'Profiles2')
insert into Profiles values (113,'Profiles3')
insert into Comment (CommentID,PhotoID,body) values (21,1,'comment1')
insert into Comment (CommentID,PhotoID,body) values (22,3,'comment2')
insert into Comment (CommentID,PostID,body) values (23,11,'comment3')
insert into Comment (CommentID,PostID,body) values (24,12,'comment4')
insert into Comment (CommentID,ProfileId,body) values (25,112,'comment5')
insert into Comment (CommentID,ProfileId,body) values (26,113,'comment6')
-- to select comments seperately for Photos, profiles and posts
select * from Comment where PhotoID is not null
select * from Comment where ProfileId is not null
select * from Comment where PostID is not null
Find something common to post, profile, etc -- I have used Entity for a lack of better word, then subtype.
Since photo comments are not the same things as post comments, I would store them in separate related tables. So I would have have:
Post:
PostComment:
Photo:
PhotoComment:
It is a poor practice to use id as the name of your PK, it makes it much harder to do reporting and much more likely to inadvertently join to the wrong table in a complex query. If you use tablenameID and consistently use the same name for Fks then it is easier to see the relationships as well.