Need tips on manipulating hierarchical data.

(SQL Server 2000, SP3)
Hello all!
My company is in the process of providing some Access Management capabilities within our
SQL Server database, and as part of this work we will be defining an association between
users of our product and some hierarchical structure (like organization/department) for
those users.
I think the table to house the hierarchical structure is pretty well-understood; in that
there will be some identifier and the table will have a field that is a reference to
itself to represent its parent identifier.
As part of our definition for this hierarchical structure, all child nodes will "inherit"
the parent node access rights. I'm not sure how best to craft the SQL that will help to
quickly identify all the parent nodes given a particular node in the tree.
I'm wondering if there are some good/recommended resources on manipulating hierarchical
data that would help me in this endeavor? Perhaps some suggestions on how I should
structure the table itself, and attendant queries.
If you know of any such material, I'd be very much obliged! :-)
John PetersonYou should definitely consider the Nested Sets approach as your hierarchy
model.
http://www.intelligententerprise.com/001020/celko1_1.shtml
http://www.dbazine.com/tropashko4.html
--
David Portas
--
Please reply only to the newsgroup
--
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:%23xWg01PYDHA.1492@.TK2MSFTNGP12.phx.gbl...
> (SQL Server 2000, SP3)
> Hello all!
> My company is in the process of providing some Access Management
capabilities within our
> SQL Server database, and as part of this work we will be defining an
association between
> users of our product and some hierarchical structure (like
organization/department) for
> those users.
> I think the table to house the hierarchical structure is pretty
well-understood; in that
> there will be some identifier and the table will have a field that is a
reference to
> itself to represent its parent identifier.
> As part of our definition for this hierarchical structure, all child nodes
will "inherit"
> the parent node access rights. I'm not sure how best to craft the SQL
that will help to
> quickly identify all the parent nodes given a particular node in the tree.
> I'm wondering if there are some good/recommended resources on manipulating
hierarchical
> data that would help me in this endeavor? Perhaps some suggestions on how
I should
> structure the table itself, and attendant queries.
> If you know of any such material, I'd be very much obliged! :-)
> John Peterson
>|||Thanks, David!
Oddly, that second link seems to suggest that Nested Sets is kind of a poor performer for
typical types of queries. Have you had any experience with Nested Sets?
Thanks!
John Peterson
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:eW2BX%23PYDHA.1580@.tk2msftngp13.phx.gbl...
> You should definitely consider the Nested Sets approach as your hierarchy
> model.
> http://www.intelligententerprise.com/001020/celko1_1.shtml
> http://www.dbazine.com/tropashko4.html
> --
> David Portas
> --
> Please reply only to the newsgroup
> --
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:%23xWg01PYDHA.1492@.TK2MSFTNGP12.phx.gbl...
> > (SQL Server 2000, SP3)
> >
> > Hello all!
> >
> > My company is in the process of providing some Access Management
> capabilities within our
> > SQL Server database, and as part of this work we will be defining an
> association between
> > users of our product and some hierarchical structure (like
> organization/department) for
> > those users.
> >
> > I think the table to house the hierarchical structure is pretty
> well-understood; in that
> > there will be some identifier and the table will have a field that is a
> reference to
> > itself to represent its parent identifier.
> >
> > As part of our definition for this hierarchical structure, all child nodes
> will "inherit"
> > the parent node access rights. I'm not sure how best to craft the SQL
> that will help to
> > quickly identify all the parent nodes given a particular node in the tree.
> >
> > I'm wondering if there are some good/recommended resources on manipulating
> hierarchical
> > data that would help me in this endeavor? Perhaps some suggestions on how
> I should
> > structure the table itself, and attendant queries.
> >
> > If you know of any such material, I'd be very much obliged! :-)
> >
> > John Peterson
> >
> >
>