Re: Tracking Record Lineage, Family, etc.
- From: Hugo Kornelis <hugo@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 05 Nov 2007 00:25:31 +0100
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
.
- Follow-Ups:
- Re: Tracking Record Lineage, Family, etc.
- From: --CELKO--
- Re: Tracking Record Lineage, Family, etc.
- From: HumanJHawkins
- Re: Tracking Record Lineage, Family, etc.
- References:
- Tracking Record Lineage, Family, etc.
- From: HumanJHawkins
- Tracking Record Lineage, Family, etc.
- Prev by Date: Re: Transaction Isolation Level
- Next by Date: Re: non key select or search
- Previous by thread: Re: Tracking Record Lineage, Family, etc.
- Next by thread: Re: Tracking Record Lineage, Family, etc.
- Index(es):
Relevant Pages
|
