Got a slight (or major, depending on how you like at it!) issue with the design of my database. My fault for not knowing what I'm doing with databases, although I have done quite a bit of reading on what I've got an issue with I can't seem to get my head around it, so here goes...
Table 1 = books
Table 2 = pages
The books table has a primary key of bookID, some fields like bookName, bookPrice and so on as well as a few foreign keys like AuthorID (which is the primary key in the table authors) and genreSubjectID (again, primary key in the genreSubject table). This all works fine and dandy. Dandy and fine.
A book has multiple pages that you can navigate through "Previous" and "Next", so there's pagination in there which comes in later on (another thread perhaps in the PHP subforum).
The pages table has a primary key of pageID, a text field called pageText which stores the text content for the page formatted with Markdown and a foreign key of bookID (which is the primary key in the books table).
Now, right now this works, but only for one page. After that it kind of messes up - Probably because I've not designed the database in the correct way for what I'd like it to be able to do!
I want to be able to have multiple pages from the pages table be associated with / belong to a particular book in the books table and then of course it's a case of making sure that works with the correct MySQL query in the PHP page.
So, my question is, where am I going wrong with the way I've designed this? How should I structure the books and pages tables so that the pages are associated with the correct books?
Many thanks for any help, pointers and especially solutions