Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [hierarchy]

For issues relating to arranging items into ordered levels.

A hierarchy is an arrangement of items (objects, names, values, categories, resources, etc.) in which the items are conceptually "above," "below," or "at the same level as" one another. Abstractly, a hierarchy is simply an ordered set or an directed acyclic graph.

The items being organized are normally refereed to as nodes, entities, or elements. A hierarchy can link entities directly or indirectly, and either vertically or horizontally. The only direct links necessary to establish hierarchy are to node's immediate superior (ie. "parent"), or to the node's subordinates (ie. "children").

However, more complex relationships can be inferred through indirect linkage, or linkage by proxy of other nodes. Conceptually, you don't have to link to a node's grandparent, it can be inferred that the parent's parent is a grandparent. Conversely, a sibling -- related horizontally to the node -- can be said to be any child of your parent and likewise no "direct" linkage is required.

183 questions
66
votes
3 answers

Find highest level of a hierarchical field: with vs without CTEs

note: this question has been updated to reflect that we are currently using MySQL, having done so, I would like to see a how much easier it would be if we switched to a CTE-supporting database. I have a self-referencing table with a primary key, id…
David LeBauer
  • 3,162
  • 8
  • 32
  • 34
23
votes
3 answers

Parent-Child Tree Hierarchical ORDER

I have to following data in SQL Server 2008 R2. SQLFiddle Schema: CREATE TABLE [dbo].[ICFilters]( [ICFilterID] [int] IDENTITY(1,1) NOT NULL, [ParentID] [int] NOT NULL DEFAULT 0, [FilterDesc] [varchar](50) NOT NULL, [Active] [tinyint]…
Archangel33
  • 417
  • 2
  • 4
  • 11
23
votes
5 answers

Anybody using HierarchyId in production? Is it reliable?

Is anybody using HierarchyId in real production with tables of reasonable size, more than a few thousand rows? Is it reliable/performant? So far I have not found anyone not affiliated with the vendor recommend it, and Paul Nielsen advises against it…
A-K
  • 7,444
  • 3
  • 35
  • 52
18
votes
4 answers

Traversing tree-like data in a relational database using SQL

Is there a way to traverse tree data in SQL? I know about connect by in Oracle, but is there another way to do this in other SQL implementations? I'm asking because using connect by is easier than writing a loop or recursive function to run the…
indyK1ng
  • 1,111
  • 2
  • 12
  • 11
16
votes
3 answers

Create hierarchy of multiple levels where each node has a random number of children

I need to create some test data that involves a hierarchy. I could make it easy and do a couple of CROSS JOINs, but that would give me a structure that is completely uniform / without any variation. That not only seems dull, but lack of variation in…
Solomon Rutzky
  • 70,048
  • 8
  • 160
  • 306
12
votes
3 answers

COUNT of rows with parent id equal to the row we have with a parent id of 0 in one query

I'd like to do the following in one query using MySQL: grab a row that has a parent_id of 0 grab a count of all the rows that have a parent_id of the row that we grabbed which has a parent_id of 0 How can I accomplish this in one query? Please let…
somejkuser
  • 797
  • 3
  • 8
  • 15
11
votes
2 answers

Is there a set based way to load/read a tree branch with HierarchyId

I am playing around with HierarchyId, and I have not figured out a set based way to do the following: insert all subtree at once retrieve all subtree at once This question is related to my previous one, and I suspect the only way to accomplish…
A-K
  • 7,444
  • 3
  • 35
  • 52
10
votes
3 answers

Parent/Child Relationship Table Design - What's The Best Practice?

I have a single table for storing 'Tasks'. A task can be a parent and/or a child. I use the 'ParentID' as the FK referencing the PK on the same table. It is NULLABLE, so if it is NULL it does not have a parent task. Example is the screenshot…
Kevin
  • 533
  • 2
  • 12
  • 20
10
votes
3 answers

Oracle: How do I query a Hierarchical table?

Background This is for the construction of some views we'll be using for reporting. I have an table of locations, the key fields being "location" and "parent". The structure that these two fields create, level-wise, are along the lines of Company…
SeanKilleen
  • 427
  • 1
  • 8
  • 18
10
votes
6 answers

Tables with hierarchy: create a constraint to prevent circularity through foreign keys

Suppose we have a table that has a foreign key constraint to itself, like such: CREATE TABLE Foo (FooId BIGINT PRIMARY KEY, ParentFooId BIGINT, FOREIGN KEY([ParentFooId]) REFERENCES Foo ([FooId]) ) INSERT INTO Foo (FooId,…
Jeroen
  • 909
  • 2
  • 14
  • 34
9
votes
1 answer

Stored Procedure to update an adjacency Model to Nested Sets Model

If you don't already know, these two models are the most common ways to store a tree in a relational DB. Adjacency Model: +-------------+----------------------+--------+ | category_id | name | parent…
azerafati
  • 221
  • 3
  • 11
9
votes
1 answer

Find friends of friends (recursively) efficiently using Postgresql

Objective: Users submit their Contact Books, and then the application looks for connections between users, according to their Phone Number. Something like "6 Handshakes" idea (https://en.wikipedia.org/wiki/Six_degrees_of_separation). Problem: Make…
Viktor Vsk
  • 213
  • 2
  • 5
9
votes
2 answers

Can I get a tree-structure from a self-referenced (hierarchical) table?

Given a hierarchical table like this: CREATE TABLE [dbo].[btree] ( id INT PRIMARY KEY , parent_id INT REFERENCES [dbo].[btree] ([id]) , name NVARCHAR(20) ); I would like to obtain the whole tree structure. For instance, using this data: INSERT…
McNets
  • 23,979
  • 11
  • 51
  • 89
9
votes
1 answer

Hierarchical permissions in a table stored hierarchy

Assuming the following database structure (modifiable if need be) ... I am looking for a nice way to determine the "effective permissions" for a given user on a given page in a way that allows me to return a row containing the Page and the…
War
  • 220
  • 2
  • 9
9
votes
3 answers

Is the hierarchyid CLR open-source?

The built-in hierarchyid is a CLR that stores paths in an efficient binary form, and provides other useful functionality. Unfortunately, there is limit to how deep the represented paths can be, and it's ~1427 for a binary tree. I would like to…
Tarnay Kálmán
  • 193
  • 5
1
2 3
…
12 13