Treestructure in SQL

[Andy Todd]

Thomas Weholt wrote:
> Hi,
> 
> I need to define tree-like structure for content categories in a simple CMS
> project. I got a table in a SQLite database which looks like this :
> 
>     CREATE TABLE category (
>       CAT_ID INTEGER PRIMARY KEY,
>       CAT_PARENT_ID integer,
>       CAT_NAME varchar(100) NOT NULL,
>       CAT_DESCRIPTION varchar(250),
>     );
> 
> It makes is possible to create a tree of categories, linking subnodes in the
> the tree to parent nodes using the CAT_PARENT_ID and it works nice, but I'm
> having trouble visualizing it.
> 
> Say I got these records in the table :
> Id, Parent-id, Name
> 1, 0, Games
> 2, 1, Counter-strike
> 3, 1, Boardgames
> 4, 0, Programming
> 5, 4, Python
> 6, 4, XML
> 7, 5, Web
> 
> Where 0 as parent-id symbolizes "root"-level of the treestructure.
> 
> Now I need either a SQL-statement compatible with SQLite or some code
> snippet that will give me :
> 
> Games
> Games, Counter-strike
> Games, Boardgames
> Programming
> Programming, Python
> Programming, Python, Web
> Programming, XML
> 
> Any clues or hints??
> 
> Best regards,
> Thomas
> 
> 

Its perfectly reasonable to store a hierarchy in a database. In fact 
Oracle has a special extension to SQL to support it (the CONNECT BY .. 
START WITH clause). When this isn't available you may want to consider 
what Joe Celko calls a 'preorder tree traversal';

http://www.intelligententerprise.com/001020/celko.shtml

Which, I think, is explained in a slightly clearer fashion here;

http://www.sitepoint.com/article/1105

Of course, if you don't want to store the left and right values as this 
technique suggests, your existing model is perfectably usable. When 
retrieving the data I'd suggest you use some kind of recursive function, 
that way you don't care how many 'levels' there are in your hierarchy.

HTH,
Andy
-- 
--------------------------------------------------------------------------------
 From the desk of Andrew J Todd esq - http://www.halfcooked.com/