April 30, 2012 at 3:51 pm
I'm working on indicator reports that use a requirements/QA software package as the data source. The application allows for a huge variety of hierarchies. In some instances, Capabilities are a parent of Features, but in other instances Features is a parent of Capabilities.
IOW, any given document may have zero to many parents and zero to many children and, unlike org chart and forum thread hierarchies, there is no root entry. Every set of hierarchies has no relationship to any other set of hierarchies. All the examples of hierarchies that I've seen are geared towards hierarchies with a root node.
The application uses the following two tables to identify hierarchies:
document <-> document_document
ID = fromdocument (parent)
or
ID = todocument (child)
What I want to do is create a "permanent" hierarchy table that is refreshed periodically and that contains the complete hierarchy for every document and additional linking keys I will need to find all the documents that have entries for any given hierarchy and link them to the appropriate metrics.
I like the idea of a "lineage" column that I have seen used in a couple of examples. Unfortunately, they all resolve to a single root. I have (potentially) thousands of roots in my table.
TIA,
Mike
May 1, 2012 at 9:05 am
This sounds like the adjacency list model would be well suited. It is not the perfect solution but it does sound like it might work in your situation. This is often used in OrgCharts.
Here is a brief description. http://www.sqlsummit.com/AdjacencyList.htm
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 1, 2012 at 9:50 am
Thanks for the reply. I've been working with the adjacency model. It requires a single root. I have thousands of "roots" and I haven't figured out how to handle that in code yet.
Mike
May 1, 2012 at 10:13 am
It can easily handle more than one root. You would just have more than 1 row where "ParentID" is null. What challenges are you running into? Jeff Moden calls this type of thing a "forest" because it is so many trees. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 1, 2012 at 11:38 am
In an adjacency list a child typically has one parent. I think in the OPs model a child can have multiple parents, in which case the permutations when traversing the hierarchy get nasty in a hurry. I am only getting into studying hierarchies, but I would hit the books on this one. A hierarchy that suits your case has likely already been documented, and consumable, high-performance SQL may already have been developed.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 1, 2012 at 12:00 pm
I'm not working with a hierarchy, but a "multi-archy". I've tried flattening-out the hierarchy into a temp table and in just 3 interations (2 child and i parent) I've gone from ~ 36K rows to 1300K rows. My table has columns for 10 parents and 10 children. At this rate, I"m looking at a billion+ rows.
I haven't determined how many levels up and down there are. I've tried the lineage column approach and I stopped at 8 levels.
May 1, 2012 at 12:16 pm
oooohhhhh......somehow I missed the details of what you were saying. This is rather challenging. The book that opc linked above is considered a great book on the topic (the link I posted is an excerpt from the same book). This could prove to be a very interesting thread as this progresses.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 1, 2012 at 5:49 pm
Mike Austin-398977 (5/1/2012)
Thanks for the reply. I've been working with the adjacency model. It requires a single root. I have thousands of "roots" and I haven't figured out how to handle that in code yet.Mike
You can have more than one root in an adjacency list. It's called an "orchard" because it contains many "trees".
--Jeff Moden
Change is inevitable... Change for the better is not.
May 1, 2012 at 5:50 pm
Mike Austin-398977 (5/1/2012)
I'm not working with a hierarchy, but a "multi-archy". I've tried flattening-out the hierarchy into a temp table and in just 3 interations (2 child and i parent) I've gone from ~ 36K rows to 1300K rows. My table has columns for 10 parents and 10 children. At this rate, I"m looking at a billion+ rows.I haven't determined how many levels up and down there are. I've tried the lineage column approach and I stopped at 8 levels.
How many documents in total?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 2, 2012 at 9:46 am
There are 321K rows in the Document table and 41K rows in the linking table.
May 2, 2012 at 5:38 pm
Mike Austin-398977 (5/2/2012)
There are 321K rows in the Document table and 41K rows in the linking table.
Thanks, Mike.
Mike Austin-398977 (5/1/2012)
I'm not working with a hierarchy, but a "multi-archy". I've tried flattening-out the hierarchy into a temp table and in just 3 interations (2 child and i parent) I've gone from ~ 36K rows to 1300K rows. My table has columns for 10 parents and 10 children. At this rate, I"m looking at a billion+ rows.I haven't determined how many levels up and down there are. I've tried the lineage column approach and I stopped at 8 levels.
Are these documents, by any chance, part of a legal "discovery" process that you're trying to build?
--Jeff Moden
Change is inevitable... Change for the better is not.
May 3, 2012 at 1:57 am
No, it's a requirements/qa application and there are many possible hierarchies. Capabilities->Features->Testing Requirements -> Test Cases -> Validation Categories is one that I'm working with at the moment. Unfortunately, the hierarchy is more or less a "gentleman's agreement"; it's not enforced and it makes the querying considerably more complex.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply