Creating a "Table of Contents" type Database

Question

OK lets start at the beginning, we are at a stage that we need to create documentation for our product. And as our product is web-based, I intend to create an on-line documentation + add a copy of this on each product.

The documentation I have planned will use PHP-HTML front and Database Back-end.

So here is what im trying to structure:

Table of Contents
    1 Getting Started
        1.1 Hello World
        1.2 Installing
            1.2.1 Windows
            1.2.2 Unix
            1.2.3 Mac
        1.3 Configuring
            1.3.1 Windows
            1.3.2 Unix
            1.3.3 Mac
    2 Hal
        2.1 Dave
        2.2 9000
        ....
        ....

So HERE is the table.

CREATE TABLE content(
    id VARCHAR(50) NOT NULL,
    parentId VARCHAR(50) DEFAULT NULL,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    dateReg TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY(id)
    FOREIGN KEY (parentId) REFERENCES `content` (id) ON DELETE CASCADE ON UPDATE CASCADE
)

Please forgive the syntax, this is only a mockup

The inserts will be as follows

INSERT INTO content ('1', 'Getting Started', 'Hello', NULL);
INSERT INTO content ('1.1', 'Hello World', 'cout << \"Hello World\";', NULL);
.... and so on

The retrieval will have to done in php. The below is only making a "table of content like structure"

$top_query = "SELECT * FROM content WHERE parentId IS NULL";
while($top_res = mysql_fetch_array($top_query)){

    print $top_res['title'] . "\n";

    $child_query = mysql_query("SELECT * FROM content WHERE parentId={$top_res['id']}");
    while($child_res = mysql_fetch_array()){

        print $child_res['title'] . "\n";
    }
}

Now i know what you will say, it needs to be recursive, yes i know, and right now lack of sleep is so bad that i cant even spell recursive (It was auto spelt by google chrome). But recursion is not my issue, i can figure that out.

My Problem: I cant help it think this is gona send out alot of queries, and the Database is gona have a heart attack per user accessing this document.

The Question: Am i on the right track, or has any one have suggestions to make this work sufficiently and be less performance intensive.

Thanks in advance

Answer 1

I think the best is to cache your table of contents in a .html file instead of calling a script which will always return the same content. You just have to recreate the cache every time the table of contents is changed.