Database for storing books by grouping, their pages, and the table of contents

Question

I'm looking for a better way to deal with the design I have here, I'm not confident it's really suitable.

To give this some context, lets say we have a bunch of groups, one group could be "SQL", and another could be "Cooking".

The SQL group would have books such as "Normalization" and "T-SQL", while cooking could have "Making pasta" and "Pizza is awesome".

The books themselves will have many pages, which may not end up being set in stone, I need to be able to change them around (and this is really a problem with my already devised scheme).

A table of contents for Pizza is Awesome might look like this:

1. History
    1. The great pizza revolution of 13901AD
    1. The first pizza
2. Making pizzas
    1. Mental preparation
        1. Think like a gazel
        1. Hunger like a lion
    1. Building the oven
        1. Real pizza is made with flamethrower.
            1. How to make a flamethrower
                1. How to get a permit for your flamethrower
3. Enjoying pizza
4. Credit

Then I run off and fill up my SQL tables like so:

Groups: pizza, SQL Books: Pizza is awesome Pages: All the 13 pages I listed in the example Table of contents.

Here's the snag: Table of contents.

Under my existing design, I'd have a table that gives each item it's own position, so "credits", "history", and other first-level items are assigned their position with an nested value of 0. Items like "Hunger of a lion" are in position 7 in the table of contents, with a nested value of 2 (because it's 2 levels deep).

This doesn't really provide for a bunch of needs without complex programming:

1. What if I wanted to move making pizzas above history? I have to re-index the entire thing!
2. What if I wanted to nest making pizzas under enjoying pizzas?
3. What if I write a whole new section?
4. How do I ensure nested levels make sense?

I basically have to update the entire index, and the "nested" level has no way to check if something makes sense (ie, the first item could be nested 6 times).

I'm looking for a better answer.

As a side note, the actual database includes revision changes of each page, and a place for extra content such as comments.

Answer 1

A number of recommendations and notes.:
1) You have to list an order of the pages somewhere (I would not rely on anything but a specific 'order' column for this) - This can either be accomplished with a 'page_number' type column (like position_in_TOC would seem to imply), or a linked-list type equivalent ('next page has this id').
2) There is a huge possibility of needing multiple groups per book; I would recommend a book_group correlation table, allowing you to have books that deal with things like ORM framworks or program database access (with groups like 'Java', 'SQL', 'C#', 'Hibernate', etc.).
3) Instead of having a 'nested factor', consider using a recursive parent-child relationship (which is precisely what the relationship is). This will also make changing which section is nested where much easier.
4) No matter what you do, you're going to have some sort of 'complex' programming. You're just going to have to deal with it.

I think a slightly more flexible design is closer to this (note: this has not been completely normalized, but should be an okay starting place):

This allows for several things:
1) Books can be part of multiple groups, or none.
2) Sections are specifically listed as children of other sections (null if they are the top). This allows sections to be moved to be inside a different parent by simply reassigning parent_section.
3) Pages and sections both have an ordinal index. This allows them to be reordered inside their containing section, without regard for their parent's index; Page 1 is page 1 of that section, not of the entire structure, eliminating the need to re-order the entire tree if only one section changes.

There are a number of things that could be done to muck with this design, maybe adding section-heading pages or something.

Answer 2

In my humble opinon you should pay attention to the data's true form. And the core-structure of yours isn't relational and tabular in its nature.

I don't think a relational database really suits your business domain naturally. A database to store hierarchical data, like an XML-database for example (eXist), might suit your business domain better, as it offers more freedom in storing and retrieving hierarchical data. eXist also integrates some features like FullText-Search nicely (Lucene integration).

You can consider a hybrid-approach, storing just a part of your data as XML in a relational database with XML features (Oracle comes to mind).

Anyway, extracting the hierarchies can be really difficult on some RDBMS, although some can retrieve such data in a single query, Oracle with it's CONNECT BY statement for example. And mofifying such hierarchies can be cumbersome, too.