One of the ways around this that I've seen is to maintain an additional field for the 'full path' info.
so that you have: table_name ----------- node_id integer auto_increment node_name varchar(50) not null parent_id integer full_path varchar(255) the obvious downside is that your application needs to maintain this info. the other option is to parse your info from the URL, tokenizing per '/' character, then do look ups recursively for each entry, finding the node you're looking for. > -----Original Message----- > From: Justin French [mailto:[EMAIL PROTECTED] > Sent: Tuesday, March 16, 2004 8:39 AM > To: MySQL > Subject: Hierarchical data design > > > Hi all, > > I've been playing around with the concepts mentioned in this article: > http://www.sitepoint.com/article/hierarchical-data-database/ > > (Short summary: Using Modified Preorder Tree Traversal, resulting in > "left and right" values for each tree node to describe the tree > structure) > > > With all this in mind, I'm hoping to emulate a "folders and pages" > hierarchical structure for a CMS, without relying on the file > system at > all. > > Here's where I get stuck: > > In a simple tree, one can easily see that using the title of > a node as > it's primary key is not smart... names can easily collide: > > Root > Products > ProductOne > About > FAQ > Support > ProductTwo > About > FAQ > Support > Services > About > > As the writer of the article suggests, numeric IDs are the > way to go. > However, I want to call the tree via the URL with name-based ID's (eg > /products/product-one/about/) rather than numeric IDs (eg /2/17/44/). > > A further complication is that this data design would allow two nodes > in the same parent node to have the same title, since the numeric key > is the ID, rather than the title. > > > When we look at a traditional file system, it's based on > unique keys AT > EACH TREE LEVEL, not unique keys for the entire tree. As > such, I don't > think the above data model is right for this application. > > The only catch is I have no idea where to look next. Hours > of Googling > has returned very little. > > Any hints on where to look next would be great. > > > --- > Justin French > http://indent.com.au > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
