[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