Sql table design for unknown drilldown depth

I have a basic question about how to design tables where you don't know how many layers deep you need to drill down, or if children records can have 0 or more children.

It would look something like this....
1
1.1
1.2
2
2.1
2.2.1
2.3.1
2.4.1.1

Or if it was a cms for a news site, you could have:
News
- local
- national
Classifieds
- Cars
-- For Sale
--- By owner
--- By dealer
-- For Lease

In this news site case it's not as simple as having 2 tables - Category & Subcategory because "News" has 1 layer deep of children, but Classifieds children can have children under that.
Anybody have a good tutorial link or explanation

Answer this question

Sql table design for unknown drilldown depth

David S. Anderson

Well the simplest solution is something like this...

Imagine a family

Name Parent

Bob

John Bob

Simon John

Now when you want to find out the depth of children you simply look to see if there are any names where the parent is the row you are looking at.

Query: Does Bob have any children

SELECT name FROM people WHERE Parent = "Bob"

And so on...

My colleague Roger Wolter tells me this is a recursive self join and that SQL Server 2005 has some kind of cool feature that helps with this but I can't recall what it is at the moment...
edukulla
Hi

this is a Tree problem and can be solved like the following:
Let's say we have a table called Nodes and contains NodeID and NodeLabel,
certainly each any node or subnode may have children. To model that, we create
another table, let's call it Tree, that have the following columns TrNodeID, TrParentID, TrDepth, TrRoot

Now let's say we have the following tree:
N1
+-- N11
+-- N111
+--N12
N2
+-- N21
etc..

The Nodes table will contain all the nodes in a flat manner.
For simplicity let's consider that the node ids are the same as the node labels,
the Tree table should be filled like the following:

TrNodeID TrParentID trDepth TrRoot
N1 N1 0 1
N2 N2 0 1
N11 N1 1 0
N11 N11 0 0
N111 N111 0 0
N111 N11 1 0
N111 N1 2 0
etc..

now suppose we want to list all the tree of N1, the query would look like
Select NodeId, NodeLabel from Nodes, Tree Where NodeID = TrParentID And TrParentID ='N1' And TrDepth >= 0
Kyle_W
Thanks guys. Very helpful.

I found a fringe benefit of structuring with the self join method and that is you can quickly throw the table through the visio org chart wizard if you're working with data that needs to be graphically represented.
http://office.microsoft.com/en-us/help/HA010774641033.aspx

They dropped it in 2003 but I hear it's back with 2007.
akin_l

Thanks Ron, I hadn't looked at this before - very interesting

CTE or Common Table Extensions in SQL Server 2005 solves the recursive query problem:

http://blogs.msdn.com/james_world/archive/2005/07/22/441736.aspx

Also http://www.sql-server-performance.com/art_cte_sql2005.asp
Sql table design for unknown drilldown depth

Answer this question