Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

Hierarchies in SQL Expand / Collapse
Author
Message
Posted Wednesday, January 28, 2009 6:37 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 26, 2012 12:00 PM
Points: 20, Visits: 14
This article caught my attention because I deal with a related problem. I oversee the field structure for a company. The difference is that the structure is time-based; and a single Area can roll up to multiple parents based on the org type in use. This is primarily so I have the ability to re-state sales based on the structure that was in place on any particular date. The second item is that I am on SQL2K, with a near-term upgrade to 2005 coming, so I could not use any of the new CTE functionality.


To do this, I had to use two table, and the "Stack" table requires a rebuild any time the hierarchy changes. (luckily not often)

My tables are as follows:
create table GeoParent (
GeoParentId int not null identity(1,1),
AreaId int not null references dbo.Geo(ID),
ParentId int not null references dbo.Geo(ID),
DateStart datetime not null,
DateEnd datetime not null,
OrgTypeId int not null references dbo.GeoOrg(ID)
)

create table GeoStack (
GeoStackId int not null identity(1,1),
AreaId int not null references dbo.Geo(ID),
Lft int not null,
Rgt int not null,
Depth int not null,
DateStart datetime not null,
DateEnd datetime not null,
OrgTypeId int not null
)

The "Stack" table is built by walking through changes to the Parent table and building the hierachy tree in a temp table, then writing it to the table after each iteration, which is currently performed in an external visual basic DLL.

Once in place, I use code similar to danielk1 to get the hierachies:
SELECT S2.AreaId 
FROM GeoStack S1, GeoStack S2
WHERE S2.Lft BETWEEN S1.Lft AND S1.Rgt
AND @date BETWEEN S1.DateStart AND S1.DateEnd
AND @date BETWEEN S2.DateStart AND S1.DateEnd
AND S1.AreaId = @tid
AND S1.OrgTypeID=@org
AND S2.OrgTypeID=@org

Any thoughts?

Thanks,
Strom
Post #645539
Posted Thursday, January 29, 2009 7:00 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
Jeff Strom: That's basically the same as what I did, you just have it in two tables where I had it in one, and you have a time-sensitivity that I didn't have. Same concept though. What you've got should be pretty good for what you need.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Post #645867
Posted Monday, October 11, 2010 11:53 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:08 PM
Points: 1,945, Visits: 2,941
The nested sets model is not as bad for dynamic hierarchies as people think. The tree structure is a table with two integers (lft, rgt) and a foreign key references to the nodes in another table. The rows are pretty short, so you get a lot of them on a data page. If you expect to be adding and removing relationships frequently, then give those pages a higher free space than you normally would for more static data.

You can also space the (lft, rgt) pairs apart by steps of 10, 100 or 1000 to allow for easy insertion. Unfortunately, this hurts the algebraic property of the Nested Sets model that makes it handy for front end display of menus. ((rgt-lft+1)/ tells me how many rows are anchored at a given node.

The algebraic property is a real boost for developers. When someone asks to see all the items under "Food", I know what to storage allocate on his side of the system. I also know what level of the hierarchy each items is at, so I can plan my sub-menus, tabs or indentation. An Adjacency List model has to build things by traversal, row by row. Recursion in SQL is really a cursor with a loop hidden under the covers.

Data integrity is also much easier; try to find cycles and orphans in an Adjacency List model without using procedural code.

An Adjacency List model has no natural ordering of siblings (unless you put in another column), so the display of a level of the hierarchy is not guaranteed to be the same.



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1002372
Posted Monday, October 18, 2010 2:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 12, 2012 3:41 PM
Points: 4, Visits: 23
nice
Post #1006034
Posted Tuesday, November 16, 2010 9:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 16, 2010 9:08 AM
Points: 1, Visits: 11
Very interesting!

Surfing the net is my hobby and posting blogs is my expression. I write different blog topics to various blog sites.
Online Bingo Reviews
Best Online Casino Games
Best Bingo Sites
Post #1021563
Posted Friday, November 19, 2010 11:25 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:09 PM
Points: 6,251, Visits: 7,411
Hey Gus,

Late to the party, I know, but you referenced this in another thread and I had a curiousity. Could you discuss the 'lazy updater' component a bit more. I'm not necessarily sure I understand how the 'temp range' vs. the 'real range' helps with update issues, since you're still locking and unlocking rows/pages/table using either column.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1023891
Posted Friday, December 10, 2010 2:21 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 5:06 PM
Points: 154, Visits: 584
Fascinating article! I have a couple of naive questions about the following bit of G code:

       Adjacency (NodeID, ParentID) as -- Adjacency Query
(select 0, ID, ParentID
from dbo.HierarchyHybrid
where ID = @NodeID_in
andexists
(select*
from dbo.HierarchyHybrid h2
where h2.TopParentID = HierarchyHybrid.TopParentID
and RangeStart is null)

1. Is it supposed to have the "select 0, " or is that just a typo? It looks like the surrounding code is expecting 2 values there, not 3. If not a typo, I don't understand the "0" part.

2. Why doesn't this work?
       Adjacency (NodeID, ParentID) as -- Adjacency Query
(select 0, ID, ParentID
from dbo.HierarchyHybrid
where ID = @NodeID_in
and RangeStart is null)

Thanks, regardless of whether you have time to reply.



"If I had been drinking out of that toilet, I might have been killed." -Ace Ventura
Post #1032885
Posted Friday, December 10, 2010 4:03 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:09 AM
Points: 870, Visits: 2,385
A very good solution and something that we've implemented in one of our solutions, the only difference is that we also have a RelationshipType table hung off the HeirarchyRelationship table.

The code snippet below, is an example of how we set this up to cater for differnt Geographic needs with an organisation.

CREATE TABLE [Common].[Geography] (
[GeographyId] INT IDENTITY (1, 1) NOT NULL,
[GeographyCode] NVARCHAR (50) NOT NULL,
[GeographyName] NVARCHAR (150) NOT NULL,
[Level] NVARCHAR (50) NULL
);
GO

CREATE TABLE [Common].[GeographyRelationship] (
[GeographyRelationshipId] INT IDENTITY (1, 1) NOT NULL,
[GeographyId] INT NOT NULL,
[ParentGeographyId] INT NOT NULL,
[RelationshipTypeId] SMALLINT NOT NULL
);
GO

CREATE TABLE [Common].[RelationshipType] (
[RelationshipTypeId] SMALLINT IDENTITY (1, 1) NOT NULL,
[RelationshipTypeName] NVARCHAR (20) NOT NULL,
[RelationshipTypeDescription] NVARCHAR (50) NOT NULL
);
GO


This allows us to create many different variations from a single list of nodes and at a variety of grains, some down only as far as sub-regions (State/County), others as deep as City Street.

Using a CTE the SQL is very simular to the Article, with only the addition of a join to the RelationshipType table and filtered on the required Type of Hierarchy.


_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1032924
Posted Friday, December 10, 2010 7:14 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 4:41 PM
Points: 112, Visits: 278
Interesting article, but I have a question, which hopefully will save me a lot of hours:
In Oracle 10 I do it in one select statement, using START WITH and CONNECT BY.
Now, migrating one of my customers to SQL 2008, is there anything close to it in SQL Server?

Thanks in advance
Post #1032998
Posted Friday, December 10, 2010 10:56 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:02 PM
Points: 21,657, Visits: 15,326
Thanks for the article Gus. I'm glad I caught it this time around.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1033139
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse