Hierarchy – Parent and child

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Hierarchy – Parent and child

Author

Topic

vijaykmr
Starting Member

3 Posts

Posted - 2007-08-02 : 08:35:00

I am designing a table to represent data in hierarchy structure, I use id and parent id to represent the data in hierarchy form:


Id | parent_id
---+-----------
1  |   0
2  |   0  
3  |   0
4  |   1
5  |   1
6  |   4
7  |   4
8  |   7
9  |   7

This structure requires complicated queries (recursive call) to find out all the child of a root node, so I have added another field for the root id.
Is this a good relational database design ? kindly suggest.


Id | parent_id | root_id
---+-----------+---------
1  |   0       |   
2  |   0       |   
3  |   0       |
4  |   1       |   1
5  |   1       |   1 
6  |   4       |   1 
7  |   4       |   1 
8  |   7       |   1 
9  |   7       |   1 
10 |   2       |   2
11 |   2       |   2
12 |   10      |   2
13 |   10      |   2

Rgds
Vijay

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-02 : 08:49:29

;WITH hierarchy (ID, ParentID, RootID)
AS (
SELECT ID, 0, ID FROM Table1 WHERE Parent_ID = 0
UNION ALL
SELECT t.ID, t.Parent_ID, t.RootID FROM Table1 AS t
INNER JOIN Hierarchy AS h ON h.ID = t.Parent_ID
)

SELECT ID, ParentID, CASE WHEN ParentID = 0 THEN NULL ELSE RootID END AS RootID FROM Hierarchy

E 12°55'05.25"
N 56°04'39.16"

vijaykmr
Starting Member

3 Posts

Posted - 2007-08-04 : 04:30:42

vijaykmr
Starting Member

3 Posts

Posted - 2007-08-04 : 04:34:36

Hi Peso,
Thanks for your query, but i want to know whether i can use the root id as i have already 2 id (id and parent id), will it be good table structure to map 3 ids( id,parent id and root id) kindly suggest.

Rgds
Vijay

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources