Data Structure - Table of Contents

Anon

I'm looking for help in determining a suitable data structure for a large online book. I've got a large (~800 pgs) manual that is currently split into 12 main sections and one appendix. Each of these main sections is then split into multiple sub-sections. I plan to have each sub-section be a seperate file that is either pointed to or stored in the database.

The tricky part is coming up with a table structure that is easily maintained and allows for sections and sub-sections to be added, deleted, updated, and rearranged by the user.

Is there a good web site available somewhere with real world examples of data structure? I have theoretical books on the subject, but they aren't always useful in the real world.

Any suggestions?

Thanks!
-Bill Glick

Anon

given info above, the following seems reasonable:

// this table has your 12 chapters
create table chpt (
chpt_id tinyint(2) primary key auto_increment,
title varchar(50) not null,
-- url varchar(100) not null ?
)
/
1 intro
2 beginnings
3 ...
/

// this table associates chapters & sections
// section.chpt_id is foreign key to chpt.chpt_id
create table section (
chpt_id tinyint(2) not null,
sec_id tinyint(4) not null,
title varchar(50) not null,
-- url varchar(100) not null, ?
primary key (sec_id,chpt_id)
)
/
1 1 why another book about this?
1 2 intended audience
1 3 online resources
2 1 my philosophy
2 2 their philosophy
2 3 the choice is clear
3 1 ...
/

if you can adopt a simple naming convention for urls, then you won't need to store urls in url columns. e.g., chpt1.html, chpt2.html, &c; sec1.html, sec2.html, &c.

e.g., update section s set s.chpt_id=1, s.sec_id=4 where s.title='my philosophy'

Anon

Thanks for the ideas Arthur.

I'm looking for a model that is a little more versatile and would allow for an infinite number of sub-sections. Here is a sample outline of what I'm talking about:

I. Chapter 1
A. Section 1
B. Section 2
1. Subsection 1
2. Subsection 2
a. Item 1
i. Subitem 1
ii. Subitem 2
b. Item 2
3. Subsection 3
C. Section 3
1. Subsection 1
2. Subsection 2
D. Section 4
II. Chapter 2
A. Section 1
B. Section 2
III. Chapter 3
IV. Chapter 4

Here's a possible solution that I came up with this morning.

create table articles (
id int autoincrement not null, # unique id of section
parent_id int, # id of parent section (NULL if top level)
section_level int not null, # level of the section
section_order int not null, # sort order with sibling sections
title varchar(50) not null,
url varchar(100), # if null, then section is a category without content
primary key (id)
)

I think I could recurse this table to display the content in an orderly fashion. Does this seem like a reasonable solution? Does anyone see any potential problems with this method?

Thanks!
-Bill Glick

Anon

oh, i see. i just checked in joe celko text book (SQL FOR SMARTIES) & i think what you've got there is probably the best way to represent hierarchical data of arbitrary depth or complexity in sql. one hasty suggestion: maybe you could add the notion of sibling; but i haven't thought that through. good luck.