For issues relating to arranging items into ordered levels.

learn more… | top users | synonyms (2)

35
votes
3answers
39k views

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 ...
15
votes
3answers
4k views

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 ...
15
votes
4answers
4k views

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 ...
13
votes
1answer
22k views

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, ...
12
votes
3answers
413 views

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. ...
10
votes
2answers
500 views

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 ...
9
votes
6answers
4k views

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 ...
9
votes
3answers
5k views

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 ...
8
votes
2answers
126 views

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 ...
7
votes
2answers
100 views

Transposing hierachical data from one VARCHAR to two INTs

THE PROBLEM The table where the data is recorded is CREATE TABLE [dbo].[Almoxarifado]( [idAlmoxarifado] [varchar](20) NOT NULL, [tipoAlmoxarifadoId] [varchar](30) NOT NULL, [entidadeId] ...
7
votes
3answers
584 views

Data Warehouse Creation

I'm fairly green when it comes to this field. I'm a data analyst at a small hospital 8 months now, out of a 2 year degree in a new career field for me. Situation We have a Cobol "database" (I ...
6
votes
1answer
584 views

I need help using hierarchyid datatype and requiring a unique constraint on a varchar field

I want to resolve a bunch of tables that had parent->child->grandchild relationships into a single table using hierarchyid identifiers. Here is what I came up with based on what I had. CREATE ...
6
votes
1answer
389 views

Hierarchical structure with limited number of records per user

I am planning schema for an affiliate network. For all hierarchical queries I am using Postgres tablefunc extension but here is another problem. Any user can refer only 3 other users at max. So for ...
5
votes
1answer
394 views

Modelling Hierarchical attributes

I am trying to figure out the data model for a retailer. The retailer has several stores across the country and they are modeled using the following hierarchy: Channel -> Zone -> City -> ...
5
votes
1answer
231 views

How can I efficiently represent inheritance down a tree?

I have a database with three "levels" of objects: A, B, and C. Table A has around 100000 rows, B has 500000, and C has 2 million. In a fit of stupidity, I designed the database to use joining tables ...
4
votes
1answer
72 views

How do I make a nullable “parent” hierarchy level?

I'm setting up my first dimensional database with SSAS, and I have this [Materials] dimension that needs a hierarchy that goes something like this: [PriceCode v] --> nullable Price Code ... ...
4
votes
1answer
1k views

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 ...
4
votes
2answers
6k views

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 ...
4
votes
1answer
514 views

Hierarchical queries without CTEs

I'm new to MySQL. The GetFamilyTree(id) function that Rolando suggested in the Find highest level of a hierarchical field: with vs without CTEs question was great but when the id is greater than 999 ...
3
votes
1answer
48 views

Create a hierarchy chain (reverse path) for each node in a tree

Given a typical closure table, how would I write a query to get a list of ids back to the root? Is there a way to do that for all the [unique] ids? I'm using Microsoft SQL Server. Given: parent ...
3
votes
1answer
96 views

A workaround for restoring HierarchyId in SQL server 2014

After installing VS 2015 CTP 4, HierarchyID stopped working in SQL Server 2014. This is the bug listed here. I am looking for a workaround. To that end, I have tried reinstalling everything ...
3
votes
3answers
2k views

Faster alternative to scalar UDF with recursion?

I have a scalar function that traverses a simple parent-child hierarchy of customers to find the ancestor that's in charge of billing. Here's a simplified version of the schema. CREATE TABLE Customer ...
3
votes
1answer
591 views

Where to place junction table?

I'm designing a database, in SQL Server 2005, and one of the tables stores 'risk assessments'. This 'risk assessments', besides the description, refers some risks, which are stored on other tables ...
3
votes
1answer
44 views

How to maintain trees and their roots in SQLite

I have a (SQLite) database with a table representing nodes in a forest (set of trees): create table "Node" ( "id" Text primary key, "parent" Text references "Node" ("id") -- parent node ...
3
votes
0answers
640 views

How to represent directed graph with multiple parents?

http://dirtsimple.org/2010/11/simplest-way-to-do-tree-based-queries.html provides an algorithm for inserting and deleting from a Closure Table. I'd like to model a similar data structure, except that ...
2
votes
2answers
154 views

Hierarchy query with 3 tables

I am not able to understand how to achieve a hierarchy accessing 3 tables. That is, I have Table1, Table2 and Table3 where: Table1 (ID_table1, name_c, size) Table2 (ID_table2, ID_table1, name_l, ...
2
votes
1answer
48 views

Surrogate vs composite key in hierarchical data structure

I'm currently evaluating a schema for a hierarchical data structure. My main problem is how I should design the schema to prevent inconsistent data (reference of foreign key in another hierarchy). The ...
2
votes
2answers
147 views

Multi level hierarchy relationship

My table design is users (id, username..., parent_id) Data is id username parent_id ------------------------------- 1 | admin | null 2 | reseller 1 | 1 3 | client 1 | 1 4 ...
2
votes
2answers
137 views

How can I re-index a column using SQL Server Compact Edition?

We have a table TemplateItem that has a column ListIndex that stores the order in which we want the items to show in the UI. This table is self-referencing / hierarchical. The query requirement is ...
2
votes
1answer
1k views

Get products of category plus all child categories till leaf nodes

We have a hierarchy which looks this: Now the problem is that most products are only connected with 1 category, which is most of the time the lowest level. I am trying to get ALL products below a ...
2
votes
1answer
202 views

adjecency list model vs nested sets

I've stumbled upon an article describing nested sets model for storing hierarchical data and was quite intrigued by the supposed performance boost. after implementing this in our database, i've found ...
2
votes
2answers
97 views

Nested table hierarchy: foreign key duplication vs. join

I have a nested table hierarchy, similar to the following one (this is an example but demonstrates the schema well enough): +---------+ +---------+ +---------+ +-------------+ | users | | ...
2
votes
1answer
27 views

How do hierarchical databases records link together?

If I had a hierarchical database containing 3 tables: course, students, grades Course would be the root table and may have 10 course records in there. Each course has its own selection of students, ...
2
votes
1answer
437 views

Get child record from specific root

I have two tables articles and topics and I am using MySQL 5.6 Articles id topic(fk on Topics.id) featured 1 1 1 2 2 0 3 3 ...
2
votes
1answer
1k views

Foreign key with multiple table options

I'm not sure if the title is very good, feel free to suggest a better one. People can "comment" on different objects of my site,similarly to StackExchange (for example they can comment on a Question, ...
2
votes
1answer
357 views

Displaying parent attributes alongside child attributes

SSAS - Parent Child hierarchy - displaying other Parent attributes alongside Child Attributes For example: Employee Table +-----+-------+------+---------------+ | KEY | NAME | ROLE | MANAGERKEY ...
2
votes
0answers
51 views

How to represent multiple unique constraints across hierarchical tables?

My (shortened) table definition is: CREATE TABLE bus ( id integer NOT NULL PRIMARY KEY, name character varying NOT NULL CHECK (name<>'') ); CREATE TABLE network_node ( id ...
2
votes
3answers
2k views

MySQL function to return breadcrumb-like string from hierarchical structure

Show create table: CREATE TABLE `a` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `parent_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `parent_id` (`parent_id`), ...
1
vote
2answers
59 views

Better deffining a parent-child relationship on a table

Our team is developing a new system and it has a Warehouse table that must have a hierarchical relationship between entries since a customer can have 1st, 2nd, 3rd and Nth degree warehouses. We first ...
1
vote
2answers
129 views

How should I delete branch from adjacency list in SQL Server?

Given a basic acyclic adjacency list like this: What is the most effective way to handle deletions of branches? My current approach uses a recursive CTE to find all descendant nodes and their ...
1
vote
1answer
2k views

HierarchyID: Get all descendants for a list of parents

I have a list of parent ids like this 100, 110, 120, 130 which is dynamic and can change. I want to get all descendants for specified parents in a single set. To get children for a single parent I ...
1
vote
2answers
1k views

Oracle hierarchical query question (start with … connect by … )

From the Oracle documentation: To find the children of a parent row, Oracle evaluates the PRIOR expression of the CONNECT BY condition for the parent row and the other expression for each row ...
1
vote
2answers
3k views

hierarchical query in oracle

Suppose I want to perform data migration operation for which I would like to load the parent tables first and then the child tables for the obvious reason. Some of these tables play dual role. Assume ...
1
vote
3answers
102 views

Avoid repeated traversing of hierarchical data when parsing a big tree frequently

I have a hierarchical/tree data structure in Oracle/RDBMS where I need to traverse the tree frequently. The tree is huge and therefore parsing it every time will not be efficient. A better way could ...
1
vote
1answer
721 views

Using Oracle hierarchical queries for transposing field value into rows

One column in my table stores list of values separated by comma (I know it's a poor design, but refactoring is not an option at the current moment). I seem to come up with a working solution but I'm ...
1
vote
1answer
37 views

Animals hierarchy use-case: multiple farms, multiple species

Let's talk about an online service where goat/sheep/cattle/llama farmers would register and keep track of their animals. One of the features of such a service is tracking parent/child relationship. ...
1
vote
2answers
67 views

Include parent node ID in hierarchical recordset of child rows

I have a table of categories which are linked together by a parent_category_id column. In this query I am able to identify all of the child categories (could be several levels of children) for a ...
1
vote
1answer
282 views

Query for hierarchical RBAC scenario

I have a SQLite database with a hierarchical role-based access control (RBAC) data model. In this model, users are granted permissions to perform certain actions against various resources either ...
1
vote
1answer
160 views

How to manage many-to-many relationships when there is also a hierarchy among the many?

I have these entities – employee, task, phase, project – and these relationships: project 1:m phase phase 1:m task employee m:m task, phase, project The first two relationships form a ...
1
vote
1answer
299 views

How to handle multiple hierarchies in a data model?

In Sql Server, there is a datatype called HIERARCHYID which helps with hierarchies. In our environment, we have patient data that needs to be classified under multiple hierarchies. Example 1: ...