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:
- What if I wanted to move making pizzas above history? I have to re-index the entire thing!
- What if I wanted to nest making pizzas under enjoying pizzas?
- What if I write a whole new section?
- 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.