17

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?

galymzhan
  • 5,505
  • 2
  • 29
  • 45

5 Answers5

14

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.

Álvaro González
  • 142,137
  • 41
  • 261
  • 360
  • No, possibility of having comment to several entities isn't issue. So I need N tables for N entities? Will CASCADE rules work in this case? – galymzhan Oct 29 '10 at 10:40
  • 1
    That's it, N tables is the idea of this design. If the DMBS supports them, cascade rules should certainly work. – Álvaro González Oct 29 '10 at 11:51
  • Thanks for explanations. I will probably choose your approach. What did you mean about improving design? – galymzhan Oct 29 '10 at 11:55
  • I meant: figure out some way to make it physically impossible that comment #123 belongs to post #456 **and** photo #789 – Álvaro González Oct 29 '10 at 11:59
  • 2
    1. That is the correct method. A many:many *relation* at the logical level is implemented as an Associative *table* at the physical level. Many tables are the nature of a Normalised database; joins do not "cost" anything. – PerformanceDBA Oct 30 '10 at 03:06
  • 2. Alvaro has corrected your column naming so that it makes sense. Always name your PK with a full qualification, including wherever it is used as an FK. "id" causes all sorts of coding errors, which are easy to avoid. I would further suggest: comment_to_post should be PostComment; comment_to_photo sb PhotoComment. – PerformanceDBA Oct 30 '10 at 03:09
  • 3. There are times when common Comments are allowable (well it eliminates duplicate Comments; allows users to choose common Comments from a drop-down list); and others, when they are not. The point is (a) you need to decide and (b) implement constraints and checks to enforce that decision. To allow common Comments, and to differentiate them: simply add a column Comment.Type (no change to the PKs and FKs). Conversely, to disallow common Comments, add the column, as a prefix to the PK [which is then (Type, CommentId) ], *and* change the FKs to suit. – PerformanceDBA Oct 30 '10 at 03:24
  • way to make it physically impossible that comment #123 belongs to post #456 and photo #789 - Possible solutions: 1. Restrict only single FK for PK 2. Do not allow multiple FK values in group of tables. – Nitul Jan 13 '19 at 09:28
13

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.

alt text

Damir Sudarevic
  • 21,891
  • 3
  • 47
  • 71
  • 3
    That is a good *generic* design technique. **IF** the four entities have common columns, and they can be normalised into a supertype, it is an advancement; **IF NOT**, it is a hindrance (eg. no common Comments) with additional definitions required (eg. disallow Profiles from having Comments). Ie, you are solving a problem that was not posted. – PerformanceDBA Oct 30 '10 at 03:39
  • I have many commentable entities and they don't have common properties. So, my supertable Entity will have only EntityId column, which I think isn't good design. That's why Alvaro's approach is more relevant to me. – galymzhan Oct 31 '10 at 05:42
  • This will work, but how can we restrict, that comments of post, will remain attached with specific post only. And can not be attached to any other entity accidentally. – Nitul Jan 13 '19 at 09:18
3

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
pavanred
  • 12,717
  • 14
  • 53
  • 59
  • 1. That is not normalised. 2. Even if you set aside the unnormalised issue, It will not work. When one FK is true, the others are all false. Allowing Nulls on indexed fields kills performance. And of course there are normalised solutions that do not have such problems. – PerformanceDBA Oct 30 '10 at 03:18
  • yes, It is not normalized. And, yes it will work. And yes, perhaps not the best in regards to performance. But we are working under some limitations like - 1. galymzhan, in his question says he wants all comments in one table. 2. Foreign keys are constraints and not indexes.There is no implicit index created on foreign key fields. – pavanred Oct 30 '10 at 05:05
  • No, it won't work in any ANSI SQL database, please try it before posting. You cannot have a null FK. I, as well as many others, have given OP a single Comments table; that is not the issue. Your answer is un-normalised, and thus has severe limitations and performance problems; that *is* the issue. If you want to carry this on, post a new question. – PerformanceDBA Oct 30 '10 at 05:32
  • I use SQL server 2008. I have tried it. Check http://stackoverflow.com/questions/4057540/is-it-possible-to-have-nulls-in-foreign-keys-in-any-ansi-sql-database I agree, again, it is not normalized and that would have its impact on performance. – pavanred Oct 30 '10 at 06:37
  • You have not tried it, and there is much that you do not understand. It is not possible to resolve the difference in commnets; and it is hijacking this thread. As stated: ** If you want to carry this on, post a new question** and post your DDL. You appear to have missed the fact that the OP's requirement is not delivered in your response; you have broken the rules he has already implemented/requested. – PerformanceDBA Oct 31 '10 at 23:08
  • Added DDL to my answer. And I can't post a new question as I have no question to ask. I have the DDL and it works fine. In your earlier comments you mentioned "You cannot have a null FK" and for that I even confirmed by posting a new question - http://stackoverflow.com/questions/4057540/is-it-possible-to-have-nulls-in-foreign-keys-in-any-ansi-sql-database – pavanred Nov 01 '10 at 08:42
0

In that case you can add an ENUM field which will contain 'photo','profile'... It will be the second part of the foreign key

MatTheCat
  • 18,071
  • 6
  • 54
  • 69
0

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:

  • PostId
  • title
  • body

PostComment:

  • Commentid
  • post_id body

Photo:

  • PhotoId
  • filepath

PhotoComment:

  • Commentid
  • photo_id
  • body

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.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • Agree re the poor column naming. But the submission is explicitly the opposite of the request, which is to Normalise Comments. – PerformanceDBA Oct 30 '10 at 03:27