需要帮助重构查询/数据库设计

I'm working on a website which will be hosting a number of books written by myself. The book contents will be stored as HTML chunks in a database.

A book contains several chapters, and each chapter contains several sections. I decided to give each book, chapter and section unique ID:s with the aim that I can move and delete sections freely and not having to update records.

So the database looks like this:

books:
    book_id int
    book_name varchar

chapters:
    book_id int references books.book_id
    chapter_id int
    chapter_no int
    chapter_name varchar // will probably be factored out to a separate table

sections:
    chapter_id int references chapters.chapter_id
    section_no int
    section_id int references section_texts.section_id

section_texts:
    section_id int
    section_text text

So for example section 5 in chapter 3 can point to the section_text which has section_id 79, and contains the text "Hello world!". Section 6 could have section_id 83 and so on.

Now I want to create a PHP method which will, given a book name, chapter number and section number, retrieve for me the section_text that is there. What I have now is done in 4 steps:

function getText($bookName, $chapter, $section)
{
    // retrieve book id
    $query = $this->db->query(
        sprintf('SELECT book_id
            FROM books` 
            WHERE book_name = %s;', $bookName));

$row = $query->row();
$bookId = $row->book_id;

// retrieve chapter id
$query = $this->db->query(
        sprintf('SELECT chapter_id
            FROM chapters` 
            WHERE book_id = %d AND chapter_no = %d;', $bookId, $chapter));

$row = $query->row();
$chapterId = $row->chapter_id;

// retrieve section id
$query = $this->db->query(
        sprintf('SELECT section_id
            FROM sections` 
            WHERE chapter_id = %d AND section_no = %d;', $chapterId, $section));

$row = $query->row();
$sectionId = $row->section_id;

// retrieve section text
$query = $this->db->query(
        sprintf('SELECT section_text
            FROM section_texts` 
            WHERE section_id = %d;', $sectionId));

$row = $query->row();
return $row->section_text;
}

So for our example above, calling getText("testBook", 3, 5) should return the retrieved text, which was Hello World!.

Although it works, it seems like the wrong way to go, creating 4 queries for fetching one section. This will be a small website so performance is not an issue, but I'm still interested in how this could be improved.

the functionallity you should have a look at is called "JOIN", which allows you to logically connect two tables within one statement. (see MySQL Reference or Wikipedia: JOIN) So, your example could look like the following:

SELECT section_texts.section_text
    FROM section_texts st
        JOIN sections s ON st.section_id = s.section_id
        JOIN chapters c ON s.chapter_id = c.chapter_id
        JOIN books    b ON c.book_id = b.book_id
    WHERE b.book_id = <book-id>
        AND c.chapter_no = <chapter>
        AND s.section_no = <section>;

Finally, I do not see a reason to separate sections and section_texts. In order to save one JOIN-Operation (and because it is a 1:1-Relation about the same entity anyhow), try to merge the section_text-Column into the sections table, thus dropping the section_texts-Table.

Hope that helps!

So start with the book you want by name, left join the chapters onto that book if the chapter matches the given chapter, then left join the sections onto that chapter if the section id matches the given section, then select the html from that section.

To visualize.. Start with all books, filter everything but the book you want, then join on all the chapters, and filter out the chapter you want (leaving you again with 1 row), then join on all the sections, and filter out the section you want (again, 1 row remains), then get whatever info you want from that 1 row.