Defining multiple foreign keys in one table to many tables

 

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?

    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.

    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.

    • 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
    链接地址: http://www.djcxy.com/p/70618.html

    上一篇: 数据库设计规则要遵循的程序员

    下一篇: 将一个表中的多个外键定义到多个表中