dongtuo7364 2012-03-11 09:56
浏览 16
已采纳

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

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.

  • 写回答

2条回答 默认 最新

  • doujing5937 2012-03-11 10:10
    关注

    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!

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题