Tracking Record Lineage, Family, etc.

Say for example, that I have a fairly complicated record of a thing
meant for the US market. Then, a Canadian version is created based on
the US one with a few changes so I want to track the connection. It is
fairly easy to make a ParentID field that would indicate the Canadian
thing descended from the US thing.

But, if I then make a British thing from the Canadian one (and I want
to do this because the Canadian one is a closer match to what the
British one needs to be), it becomes more complicated... I can put the
ID of the Canadian one in the ParentId field of the British one. But
then I need to run multiple queries to build the complete lineage back
to the original US record.

More so, if I want to allow n-number of levels to the relationships
between these things, it becomes even more difficult.

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?

Thanks in advance!

Re: Tracking Record Lineage, Family, etc.

On Fri, 02 Nov 2007 13:09:19 -0700, HumanJHawkins
wrote:

SQL Server 2005 has new support for such queries. Check out Books
Online on the topic "recursive queries".

-Tom.


>Say for example, that I have a fairly complicated record of a thing
>meant for the US market. Then, a Canadian version is created based on
>the US one with a few changes so I want to track the connection. It is
>fairly easy to make a ParentID field that would indicate the Canadian
>thing descended from the US thing.
>
>But, if I then make a British thing from the Canadian one (and I want
>to do this because the Canadian one is a closer match to what the
>British one needs to be), it becomes more complicated... I can put the
>ID of the Canadian one in the ParentId field of the British one. But
>then I need to run multiple queries to build the complete lineage back
>to the original US record.
>
>More so, if I want to allow n-number of levels to the relationships
>between these things, it becomes even more difficult.
>
>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?
>
>Thanks in advance!

Re: Tracking Record Lineage, Family, etc.

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

Re: Tracking Record Lineage, Family, etc.

Thanks much. This lead me to exactly what I needed. Also, a link that
might help future people who might find this thread is:
http://dev.mysql.com/tech-resources/articles/hierarchical-da ta.html

Cheers!

Re: Tracking Record Lineage, Family, etc.

>> It's called, how unoriginal, "Joe Celko's Trees and Hierarchies in SQL for Smarties". <<

We tried calling it "An Illustrated History of Flogging in British
Navy" but it just did not work as well, in spite of being very
original :) (I also wanted to drop the ".. for Smarties" part).