Men I'd really be thankful if a person may help me with this particular. I've got a schema with 4 tables highly relevant to this Books, Groups, ReadingList, Comments. A person from the system can enroll in a group and add books to that particular group's reading through list. A group's book list is symbolized through the table ReadingList:
ReadingList ------------- Id (auto_increment) ReadingListID BookID (pk) GroupID (pk)
BookID and GroupID are positioned as an amalgamated primary answer to ensure no book can be displayed on the groups reading through list two times. These two fields possess a fk defined towards the relevant table Books/Groups. Now my problem comes when attempting to setup rapport between your Comments table and also the ReadingList. The idea is the fact that each unique entry within the ReadingList might have many comments (1..*) so essentially one book on the groups reading through list might have many comments connected within. Your Comments Ought To table appears like this:
Comments ----------- Id (pk, auto_increment) ReadingListID UserName Comment TimeStamp
My logic is the fact that a fk be setup from Comments (ReadingListID) to ReadingList (ReadingListID) however i am clearly problematic when i obtain a 'no primary or candidate secrets in recommended table' error.
I've attempted a number of such things as making ReadingListID &lifier Id within the Comments table an amalgamated key and making ReadingListID a pk within the ReadingList table etc, however i cannot get my mind round this. Please tell me basically weren't obvious enough.
integer, serial number (1,2,3..) for every
GroupID, BookID combination. It may be easily acquired when designing a brand new comment using
select coalesce(max(BookInGroupCommentNo), 0) + 1 from ReadingList where GroupID = some_group_id and BookID = some_book_id ;
Eliminate individuals auto-increment
You skill is use alter the primary key of ReadingList in the composite (BookID+GroupID) towards the ReadingListID column, your FK works. After that you can produce a unique constraint or index over the BookID+GroupID posts to enforce that data integrity.
I am confused, though, how you get a car-increment on ReadingList (Id) that is outside of your ReadingListID column. Possibly you need to drop either. I believe, the way in which your schema appears to become setup, possibly ReadingList.Id may be the PK, and Comments.ReadingListID is really a FK to that particular column.
Like ReadingList's Primary Secret is really an amalgamated key (BookID, GroupID). You need to improve your Comments table and also have BookID and GroupID rather than ReadingListID.
Why have you got ReadingListID? You do not need it since you have your composite primary key defined.
I would recommend:
ReadingList ------------- ReadingListID (pk, auto_increment) BookID (fk) GroupID (fk) + Unique index on (BookID, GroupID) Comments ----------- CommentID (pk, auto_increment) ReadingListID UserName Comment (maybe "Content", "Value" or "Body" could help avoid typing comments.comment) TimeStamp