470,596 Members | 1,564 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,596 developers. It's quick & easy.

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!

Nov 2 '07 #1
4 1646
On Fri, 02 Nov 2007 13:09:19 -0700, HumanJHawkins
<JH******@Locutius.Comwrote:

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!
Nov 3 '07 #2
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
Nov 4 '07 #3
<CUT>

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/...ical-data.html

Cheers!
Nov 16 '07 #4
>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).
Nov 17 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

12 posts views Thread by Dan Greenblatt | last post: by
4 posts views Thread by Tim Graichen | last post: by
reply views Thread by LiveTecs | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.