[thelist] Database Schema Suggestions -- Follow up question

Burhan Khalid thelist at meidomus.com
Sat May 8 08:54:07 CDT 2004


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?

   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).

   The other idea is to create a new nodes table for each main category. 
  Sticking with dmoz.org, this would mean a new table for Arts, a new 
one for Games, etc. My current database structure is like this :

mysql> describe dir_nodes;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      |      | PRI | NULL    | auto_increment |
| title | varchar(255) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> describe dir_tree;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| leftid  | int(11) |      |     | 0       |       |
| rightid | int(11) |      |     | 0       |       |
+---------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

I would like to keep this database layout if possible.  dir_nodes would 
contain the text for the node, and there is a separate table (not 
listed) that would hold node descriptions, urls, and other meta info.

Any thoughts?

Thanks again,
Burhan


More information about the thelist mailing list