RDBMS strategies for storing heirarchical lists? querying/outputting them?

Question

I'd like to store a fair bit of text data with the following structure:

1. Overall Text A
  1.1 Subsection Text
       1.1.1 Clarification A
       1.1.2 Clarification B
  1.2 Another Subsection
       1.2.1 Don't forget A
       1.2.2 Don't forget B
       1.2.3 or C!
2. Overall Text B
  2.1 getting the idea yet?

Section levels would range from 1 (flat) to 4, though there is no absolute max. Each item would be at least one paragraph's worth of text. And it's important to preserve the numbering scheme.

I'm using MySQL and PHP for the site, and this concerns a section I'd like to add. How does one best store these data and relationships in an RDBMS, and then how does one get them out again and display them logically with PHP?

Answer 1

Its simple to create a database structure which represents a hierarchy:

 CREATE TABLE nodes (
    node_id int NOT NULL,
    parent_node_id int,
    node_data varchar(100),
    PRIMARY KEY node_id);

But maintaining the numbering system is a bit more complex - however CSS can do this for you.

An alternative approach to managing hierarchical data which is a lot more flexible is described here.

You may also find phplm useful.

Answer 2

A nested set would give you the benefit of storing hierarchical data in an efficient way. However, it can be somewhat of a pain to work with. Most of the time, and with simple structures, it behaves fine and efficient though!

MySQL example: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

I've used this method myself in a couple of projects, and besides some headaches on path finding, I have no complains. :)