hierarchial data - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

hierarchial data

Author

Topic

jaycards
Starting Member

3 Posts

Posted - 2006-03-10 : 14:34:31

I need to retreive hierarchial data.
I got a table tblCore(TrackItem, SNO, TrackChildItem, Quanitiy)

The data in the tblCore looks some thing like this

TrackItem SNO TrackChildItem Quantity
------------------------------------------------------------------------
101 1 2001 1
101 2 2019 1
101 3 2022 1
101 4 2025 5
101 5 2026 1
101 6 2027 1
101 7 2028 2
101 8 2029 1
101 9 2030 1
101 10 2031 1
101 11 2045 1
2001 1 2002 1
2001 2 2005 2
2002 1 2003 1
2002 2 2004 1
2005 1 2006 1
2005 2 2010 3
2005 3 2012 1
2005 4 2013 1
2005 5 2014 1
2005 6 2015 3
2006 1 2007 1
2006 2 2008 1
2006 3 2009 1
2010 1 2011 1
2015 1 2016 1
2015 2 2017 1
2015 3 2018 1
2019 1 2020 4
2019 2 2021 1
2022 1 2023 1
2022 2 2024 1
2031 1 2032 1
2031 2 2035 2
2031 3 2036 1
2031 4 2037 1
2031 5 2038 1
2031 6 2039 1
2031 7 2040 1
2031 8 2041 6
2031 9 2042 2
2031 10 2043 1
2031 11 2044 2
2032 1 2033 1
2033 1 2034 1
------------------------------------------------------------------------------------------------------------------------------------------------------
Quiz 1) Display all child data in hierarchial manner, When I pass input parameter to stored procedure.

Say, I pass 101 as input parameter, my output recordset should look like (list all childs linked to 101)

Index TrackElement Quantity
------------------------------------------------------------------------
1 2001 1
1.1 2002 1
1.1.1 2003 1
1.1.2 2004 1
1.2 2005 2
1.2.1 2006 1
1.2.1.1 2007 1
1.2.1.2 2008 1
1.2.1.3 2009 1
1.2.2 2010 3
1.2.2.1 2011 1
1.2.3 2012 1
1.2.4 2013 1
1.2.5 2014 1
1.2.6 2015 3
1.2.6.1 2016 1
1.2.6.2 2017 1
1.2.6.3 2018 1
2 2019 1
2.1 2020 4
2.2 2021 1
3 2022 1
3.1 2023 1
3.2 2024 1
4 2025 5
5 2026 1
6 2027 1
7 2028 2
8 2029 1
9 2030 1
10 2031 1
10.1 2032 1
10.1.1 2033 1
10.1.1.1 2034 1
10.2 2035 2
10.3 2036 1
10.4 2037 1
10.5 2038 1
10.6 2039 1
10.7 2040 1
10.8 2041 6
10.9 2042 2
10.10 2043 1
10.11 2044 2
11 2045 1

Say, I pass 2031 as input parameter, my output recordset should look like (list all childs linked to 2031)

Index TrackElement Quantity
-----------------------------------------------------------------------

1 2032 1
1.1 2033 1
1.1.1 2034 1
1.2 2035 2
1.3 2036 1
1.4 2037 1
1.5 2038 1
1.6 2039 1
1.7 2040 1
1.8 2041 6
1.9 2042 2
1.10 2043 1
1.11 2044 2

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-03-10 : 14:56:23

Well, it is very odd that your layout includes a column for CHILD item. Normally, (and by "normally", I mean absolutely every succesful hierarchical implementation I have ever seen in a 15 years of consulting), the layout will indicate the PARENT item ID. With this method the ItemID constitutes a unique and primary key. As you can see from your data, using ChildID forces you to add an additional SNO column to maintain integrity.
So if there is any way you can reverse this layout I would strongly advise you to do so, as I predict MANY more problems for you down the road if you continue with this method.

jaycards
Starting Member

3 Posts

Posted - 2006-03-10 : 15:20:45

I do not think, I could reverse my table layout.
reason,

TrackItem SNO TrackChildItem Qty
--------------------------------
101 1 2001 1
101 2 2019 1
101 3 2022 1
101 4 2025 5
101 5 2026 1
101 6 2027 1
101 7 2028 2
101 8 2029 1
101 9 2030 1
101 10 2031 1
101 11 2045 1

Now say, a track element 201, 301 can have these set of track child items listed below.
201 1 2027 1
201 2 2028 2
201 3 2029 1

301 1 2030 1
301 2 2028 1
301 3 2045 1

So, you wouldn't be able to justify if tblCore is different
tblCore(TrackElement, SNO, TrackChildItem, Quantity).
I would appreciate, if you could answer to actual question posted.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-03-10 : 16:42:54

How can 2028 be a child records of both 201 and 301?

There are lots of algorithms available for dealing with hierarchical data with a standard parent/child relationship. But your child/parent relationship just doesn't make sense, and neither does the resulting data, and I don't know of any algorithms for dealing with it.

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2006-03-10 : 20:30:52

Since a node can have multiple parents you might want to store this information in a separate table.

TrackItem
TrackItem PK
SNO
Quantity

TrackItemParent
TrackItem PK
ParentTrackItem PK

I know this doesn't answer your question, but maybe if you stored your information like this query would be easier.

jaycards
Starting Member

3 Posts

Posted - 2006-03-12 : 10:55:21

Guys,

This is about dealing many -to - many relationship.
A parent can have many child and also, the a child can be listed under different parents.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-03-12 : 19:42:25

A many-to-many relationship is NOT the same thing as a hiearchical relationship. Are you mixing the two? Can an item be its own parent? Can item relationships be circular? How do you intend to enforce whatever relational integrity you need to apply?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-03-13 : 11:19:35

Here is an article I wrote on traversing hierarchical relationships. You should be able to adapt this "accumulator" table method to your schema, and it has the benefit of not choking on circular relationships. You will need to add a column for tracking the level of the hierarchy, and format it the way you want. I hope this helps:

The most flexible and robust method of storing hierarchical data in a database is to use a table with a recursive relationship. In this design, each record has an associated parent record ID that indicates its relative place in the hierarchy. Here is an example:

CREATE TABLE [YourTable]
([RecordID] [int] IDENTITY (1, 1) NOT NULL ,
[ParentID] [int] NULL)

The challenge is to find a way to return all the child records and descendants for any given parent record.

While recursion is supported within SQL Server, it is limited to 32 nested levels and it tends to be ineffecient because it does not take full advantage of SQL Server's set-based operations.

A better algorithm is a method I call the "Accumulator Table".

In this method, a temporary table is declared that accumulates the result set. The table is seeded with the initial key of the parent record, and then a loop is entered which inserts the immediate descendants of all the records accumulated so far which have not already been added to the table.

Here is some skeleton code to show how it works:

--This variable will hold the parent record ID who's children we want to find.
declare @RecordID int
set @RecordID = 13

--This table will accumulate our output set.
declare @RecordList table (RecordID int)

--Seed the table with the @RecordID value, assuming it exists in the database.
insert into @RecordList (RecordID)
select RecordID
from YourTable
where YourTable.RecordID = @RecordID

--Add new child records until exhausted.
while @@RowCount > 0
insert into @RecordList (RecordID)
select YourTable.RecordID
from YourTable
inner join @RecordList RecordList on YourTable.ParentID = RecordList.RecordID
where not exists (select * from @RecordList CurrentRecords where CurrentRecords.RecordID = YourTable.RecordID)

--Return the result set
select RecordID
from @RecordList

This method is both flexible and efficient, and the concept is adaptable to other hierarchical data challenges.

For a completely different method of storing and manipulating hierarchical data, check out Celko's Nested Set model, which stores relationships as loops of records.

http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=145525%5D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources