|
|
Posted by Hugo Kornelis on 11/04/07 23:25
On Fri, 02 Nov 2007 13:09:19 -0700, HumanJHawkins wrote:
(snip)
>This type of issue has come up repeatedly in my work, so I assume it
>is not a new problem and that there may be a "best practice" for
>handling it.
>
>Can anyone offer any advice, an answer, or point me toward a place
>where I may find the answer?
Hi HumanJHawkins,
It is indeed a common issue, as you are modeling a hierarchy. The most
common example of hierarchies in database literature is the work
hierarchy (Steve reports to Jack, who reports to Michelle, etc).
There are several models commonly used for representing hierarchies,
each with their pros and cons. The most common models are:
* Adjacency List Model
* Nested Sets Model
* Materialized Path Model
I'm sure that google should bring up plenty of examples of each.
Joe Celko has dedicated a complete book to the subjects of trees and
hierarchies in SQL. I haven't read it myself so I don't know if I should
recommend it or not, but now at least you know it exists. It's called,
how unorginal, "Joe Celok's Trees and Hierarchies in SQL for Smarties".
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Navigation:
[Reply to this message]
|