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.

Thank you!

enter image description here

The BookInGroupCommentNo is 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 IDs and ReadingListID from ReadingList and Comment tables.

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