One to Many Database Relationship

Guys I would really appreciate it if someone could help me with this. I have a schema with 4 tables relevant to this question Books, Groups, ReadingList, Comments. A user of the system can join a group and add books to that group's reading list. A group's book list is represented by the table ReadingList:

Rea开发者_如何学编程dingList
-------------
Id (auto_increment)
ReadingListID
BookID (pk)
GroupID (pk)

BookID and GroupID are set up as a composite primary key to ensure no book can appear on a groups reading list twice. Both these fields have a fk defined to the relevant table Books/Groups. Now my problem comes when trying to set up a relationship between the Comments table and the ReadingList. The theory is that each unique entry in the ReadingList can have many comments (1..*) so basically one book on a groups reading list can have many comments associated with in. The Comments table looks like this:

Comments
-----------
Id (pk, auto_increment)
ReadingListID
UserName
Comment
TimeStamp

My logic is that a fk be set up from Comments (ReadingListID) to ReadingList (ReadingListID) but I am clearly flawed as I get a 'no primary or candidate keys in referenced table' error.

I have tried a variety of things like making ReadingListID & Id in the Comments table a composite key and making ReadingListID a pk in the ReadingList table etc, but I just can't get my head round this. Please let me know if I haven't been clear enough.

Many thanks!

The BookInGroupCommentNo is integer, serial number (1,2,3..) for each GroupID, BookID combination. It can be easily obtained when creating a new comment using

select
    coalesce(max(BookInGroupCommentNo), 0) + 1  
from ReadingList
where GroupID = some_group_id
  and BookID  = some_book_id ;

Get rid of those auto-increment IDs and ReadingListID from ReadingList and Comment tables.

What you can do is use change the primary key of ReadingList from the composite (BookID+GroupID) to the ReadingListID column, then your FK will work. You can then create a unique constraint or index across the BookID+GroupID columns to enforce that data integrity.

I'm confused, though, why you have an auto-increment on ReadingList (Id) which is separate from your ReadingListID column. Perhaps you should drop one or the other. In my mind, the way your schema seems to be set up, perhaps ReadingList.Id is the PK, and Comments.ReadingListID is a FK to that column.

That's because ReadingList's Primary Key is actually a composite key (BookID, GroupID). You should update your Comments table and have BookID and GroupID instead of ReadingListID.

Why do you have ReadingListID? You don't need it since you already have your composite primary key defined.

I suggest:

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