Self-Referencing Query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Aaron Weiker (aaron_at_sqlprogrammer.org)
Date: 02/01/05 

Date: Mon, 31 Jan 2005 16:24:34 -0800

Problem: I want to create a menu tree by looking up the table heirarchy from
a single table. For instance I have one row that points to a previous to
identify that it belongs in that "folder". Think of it as a file system type
approach. Ideally I want to represent this as an XML file as I already wrote
a XSLT file that parses it and creates a nice tree on a web page. For an
example check out the webpage at http://stats.weiker.org/. It uses this XML
File to pull the content from. http://stats.weiker.org/links.xml
Here is the DDL for the table.

   CREATE TABLE dbo.Menus
   (
      [Id] int not null identity(1,1),
      [ParentId] int,
      [Name] varchar(128) not null,
      [Order] int not null
         CONSTRAINT DF_Menus_Order DEFAULT(0),
      [Url] varchar(1024),
      [CreateDate] smalldatetime not null
         CONSTRAINT DF_Menus_CreateDate DEFAULT(GETDATE()),
      CONSTRAINT PK_Menus PRIMARY KEY CLUSTERED ([Id]),
      CONSTRAINT UK_Menus UNIQUE ([Name], [ParentId]),
      CONSTRAINT FK_Menus_Menus
         FOREIGN KEY ([ParentId])
         REFERENCES dbo.Menus([Id])
   )

The problem I keep running into creating the heirarchy. For example

Folder 1
--> Item A
--> Item B
--> Item C
-->Folder 2
-->--> Item A
-->--> Item F
Folder 999
--> Item Z
--> Item Y

The only solution I currently see is to look through each item and then retrieve
the items that belong to that and perform recursively. Whenever I see this
pattern I immediately see my CPU soaring and I break into a cold sweat. Maybe
not that bad but you get the picture.

So have I over normalized into this nice little design that I really want
to work but in reality it is going to cost too much to do?

Or am I just crazy and should just stick to using a pure XML file approach?
Right now I'm thinking this might be much simpler, but that would require
accepting defeat. 

--
Aaron Weiker
http://aaronweiker.com/
http://sqlprogrammer.org/

Relevant Pages

  • Re: Self-Referencing Query
    ... Obout has a tree control for ASP with an example that uses this approach. ... Ideally I want to represent this as an XML file as I ... > The problem I keep running into creating the heirarchy. ... > Folder 1 ...
    (microsoft.public.sqlserver.programming)
  • Re: file problem
    ... like tdom, perhaps, to parse and search the xml file, and file rename ... the resulting tree under /tmp/mytest is: ... puts "processing $filename" ... global compareTime destdir errors ...
    (comp.lang.tcl)
  • RE: saving layout of TreeView control
    ... If you want however to retain the layout of the tree view even after you ... "dshemesh" wrote: ... that in the xml file. ... Then, I expand some of ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: How to get the XML path of an XmlNode?
    ... In this link page I see only XML file. ... An XmlNode has a ParentNode method, so you can just walk back up the tree, ... Why do you need the XPath expression? ...
    (microsoft.public.dotnet.xml)