[thelist] Database Schema Suggestions -- Follow up question

Joshua Olson joshua at waetech.com
Sat May 8 12:23:42 CDT 2004

> -----Original Message-----
> From: Burhan Khalid
> Sent: Saturday, May 08, 2004 9:54 AM
> Greetings Everyone:
>    Thanks for the great links on storing hierarchical data, I am happily
> on my way to designing the database layout.
>    Ran into one problem.  All the examples talk about a root node, but
> in an application such as dmoz.org ... what would be the root node? Or
> are each main categories considered root nodes?

There should be only one root node.  All the main categories would be
children of that root node.  If you don't want the end users to know about
the root node, simply don't display it.  Or, you could use the id of 0 to
represent the root node.  Since identity counters start at 1, usually, this
means that the root node would really never exist, but the parent_id of the
first level children would still be 0.

>    I thought about creating a boolean field in my table and setting it
> to 1 if the node was a root node, but this seems to defeat the
> purpose  of the original design (I also ordered that SQL for
> Smarties book).

No boolean is necessary... just use "where parent_id  = 0" to find them.

Joshua Olson
Web Application Engineer
WAE Tech Inc.

More information about the thelist mailing list