Hi all,
I have a requirement to store hierarchical data in a SQL database. I've
seen several different solutions posted here in the past on how to retrieve
hierarchical data.
In an effort to simplify the storage and retrieval of this data, I was
thinking of initally inserting the data into the table with some type of
"Level" or "Position" column which indicates the position in the hierarchy
of each item. I will need to insert and remove items in the hierarchy
somewhat frequently.
For an insertion into the hierarchy, if I know where the new item's position
should be, I could just insert the new item into the table with that
position number, then increment the item which previously had that
particular position and all the items below it by one. Removing an item
from the hierarchy would follow similar logic.
Are there any drawbacks to storing the hierarchical data this way? Is there
any reason for me to avoid this method of maintaining a "Position" column
for each item?
Any commenets are appreciated,
BenA "hierarchy level" column seems unlikely to be useful for subtree
maintenance in an Adjacency List model (I am assuming adjacency list
because you didn't specify what other model you might be using). In
fact it almost certainly hinders inserts because it is redundant data
that needs to be updated each time - probably on many rows for each row
inserted. Don't store the hierarchy level unless you regularly need to
query that information and have a good reason to denormalize.
For subtree maintenance, Materialized Path is typically the lowest cost
model. Nested Sets is cheap for pruning the tree but unpredictably
costly for grafting on subtrees.
David Portas
SQL Server MVP
--|||Check out this article from Joe Celko:
http://www.dbazine.com/ofinterest/oi-articles/celko24
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Ben Amada" <ben@.REpoMOweVErpick.com> wrote in message
news:OL%23BJww0FHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I have a requirement to store hierarchical data in a SQL database. I've
> seen several different solutions posted here in the past on how to
> retrieve hierarchical data.
> In an effort to simplify the storage and retrieval of this data, I was
> thinking of initally inserting the data into the table with some type of
> "Level" or "Position" column which indicates the position in the hierarchy
> of each item. I will need to insert and remove items in the hierarchy
> somewhat frequently.
> For an insertion into the hierarchy, if I know where the new item's
> position should be, I could just insert the new item into the table with
> that position number, then increment the item which previously had that
> particular position and all the items below it by one. Removing an item
> from the hierarchy would follow similar logic.
> Are there any drawbacks to storing the hierarchical data this way? Is
> there any reason for me to avoid this method of maintaining a "Position"
> column for each item?
> Any commenets are appreciated,
> Ben
>|||Hi David,
This is my first time dealing with hierarchies, so I'm not sure if the model
I'm working with is an Adjacency List model or not.
Below, I've included some DDL and insert statements on the type of data I'm
going to be storing -- the data below is for a single hierarchy tree I'll be
storing -- I'll actually be storing dozens of these trees, so I'll obviously
need another column in the table to identify the Hierarchy ID. There aren't
that many rows per hierarchy though, which is why I was thinking of storing
a "Position" or "Level" column.
When I retrieve the data from the table, the desired order of the data is
the same order of the INSERT statements below -- so "catOuter", "catOne",
"spnOne", etc.
I see myself having two or three options when retrieving the data:
(1) Create and maintain a "Position" column.
(2) Create a stored procedure or UDF which calls itself recursively to
retrieve the hierarchy in the desired order.
I suppose a 3rd option would be some set-based solution which is what I
usually try to do, but for hierarchies, I'm not sure how to implement a
set-based solution.
After seeing the sample data below, I'm interested in which option (1, 2 or
3) you think might work best?
Thanks again,
Ben
--
create table PageElements
( ElementID varchar(25) primary key,
ParentID varchar(25) )
insert into PageElements
(ElementID, ParentID)
select 'catOuter', NULL union all
select 'catOne', 'catOuter' union all
select 'spnOne', 'catOne' union all
select 'imgOne', 'catOne' union all
select 'catTwo', 'catOuter' union all
select 'spnTwo', 'catTwo' union all
select 'catThree', 'catOuter' union all
select 'spnThree', 'catThree' union all
select 'spnFour', 'catThree' union all
select 'spnFive', 'catThree' union all
select 'imgTwo', 'catThree' union all
select 'imgThree', 'catThree' union all
select 'spnSix', 'catThree' union all
select 'spnSeven', 'spnSix' union all
select 'spnEight', 'spnSix' union all
select 'spnNine', 'spnSix' union all
select 'spnTen', 'spnSix' union all
select 'spnEleven', 'spnSix' union all
select 'imgFour', 'spnSix'|||SriSamp wrote:
> Check out this article from Joe Celko:
> http://www.dbazine.com/ofinterest/oi-articles/celko24
Hi SriSamp,
Thank you for the helpful link! I'll take a look at it.
Ben|||Also buy the book TREES & HIERARCHIES IN SQL; it has more details and
other methods.|||If you want to store the items in order, you might want to look at a linked
list solution.
This way you only need to edit the item after the record you're inserting.
id parent
1 <null>
2 1
3 2
4 3
insert 5 between 2 & 3.
begin trans (serializable)
insert table values ( 5 , 2 )
update table set parent = 5 where parent = 2 and id != 5
commit trans
Then you end up with
id parent
1 <null>
2 1
5 2
3 5
4 3
HTH
"Ben Amada" <ben@.REpoMOweVErpick.com> wrote in message
news:OL%23BJww0FHA.1032@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I have a requirement to store hierarchical data in a SQL database. I've
> seen several different solutions posted here in the past on how to
retrieve
> hierarchical data.
> In an effort to simplify the storage and retrieval of this data, I was
> thinking of initally inserting the data into the table with some type of
> "Level" or "Position" column which indicates the position in the hierarchy
> of each item. I will need to insert and remove items in the hierarchy
> somewhat frequently.
> For an insertion into the hierarchy, if I know where the new item's
position
> should be, I could just insert the new item into the table with that
> position number, then increment the item which previously had that
> particular position and all the items below it by one. Removing an item
> from the hierarchy would follow similar logic.
> Are there any drawbacks to storing the hierarchical data this way? Is
there
> any reason for me to avoid this method of maintaining a "Position" column
> for each item?
> Any commenets are appreciated,
> Ben
>
I will hopefuly use it when I will work on some enterprise level application in the future.
