Questions tagged [hierarchy]
For issues relating to arranging items into ordered levels.
182 questions
66
votes
3
answers
82k
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 ...
- 3,162
23
votes
3
answers
87k
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,
[FilterDesc]...
- 417
22
votes
5
answers
9k
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 ...
- 7,404
18
votes
4
answers
10k
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 than ...
- 1,111
16
votes
3
answers
13k
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. ...
- 69.5k
12
votes
3
answers
34k
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
...
- 797
11
votes
2
answers
894
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 previous ...
- 7,404
10
votes
6
answers
6k
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 Foo ...
- 899
10
votes
3
answers
8k
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 ...
- 427
10
votes
3
answers
27k
views
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 ...
- 533
9
votes
3
answers
436
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 ...
- 193
9
votes
2
answers
26k
views
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 ...
- 23.9k
9
votes
1
answer
3k
views
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 ...
- 220
9
votes
1
answer
7k
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 ...
- 221
9
votes
1
answer
2k
views
How to convert a Postgresql ltree structure to a nested set by using VIEW?
I would like to ask a question about a way for transforming a PostgreSQL ltree structure to a nested set structure with only one query by using only VIEWs.
For example, I have a table which has data ...
- 193
9
votes
1
answer
683
views
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....
- 213
8
votes
1
answer
4k
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 ...
- 1,029
7
votes
2
answers
247
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] [...
- 591
7
votes
3
answers
665
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 ...
- 1,225
7
votes
2
answers
2k
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 | | ...
- 214
6
votes
1
answer
904
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 ...
- 653
6
votes
1
answer
673
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 ...
- 291
5
votes
2
answers
50k
views
Parent Child Relation in same Table
I have a table storing parent/child records as such:
+-------+------------+---------+---------+------------+-----------+
|custid | custname | deptid | company |parentcustid| enrolled |
+=======+==...
- 435
5
votes
2
answers
368
views
Find missing folders paths in split hierarchies
I have a table which contains folders' paths. This table contains four columns:
DirID - The folder's ID.
BaseDirID - The ID of the first folder in the hierarchy. So all the folders (paths) from the ...
- 153
5
votes
1
answer
4k
views
Condition in WHERE vs condition in CONNECT BY
Can anyone explain to me the difference between the following two queries? Even though they seem the same the results are different.
select concept.concept_id, concept.PARENT_ID
from ...
5
votes
1
answer
1k
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 -> ...
- 153
5
votes
1
answer
310
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 ...
- 352
4
votes
3
answers
16k
views
How to build a table for a private messaging system that supports replies?
I'm writing a private messaging web application in PHP, the application allows replies such that when you view a message, you also get to see to what was that a reply to, and to what was that a reply ...
- 141
4
votes
3
answers
119
views
How can I pass on a value in the hierarchy based on the value of the parent using an hierarchial query?
I have a table with categories and another table with a discount for categories per user:
create table category
(
id NUMBER NOT NULL,
parent_id NUMBER,
name nvarchar2(255),
PRIMARY KEY( id)
)...
- 145
4
votes
1
answer
2k
views
How to do hierarchical queries without CTE tables or recusive queries in Snowflake? (parent, child relationships)
I have several tables in Postgres that we are migrating to Snowflake. Some of these table are hierarchical (a foreign key point to the same table), normally to query this data I use CTE tables and/or ...
- 543
4
votes
1
answer
1k
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 ...
- 173
4
votes
1
answer
683
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
...
...
- 914
4
votes
1
answer
1k
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 ...
- 147
4
votes
2
answers
1k
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 ...
- 41
4
votes
1
answer
3k
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, ...
- 313
3
votes
2
answers
1k
views
CTE get all descendants with parents
Schema:
CREATE TABLE item (
id int primary key,
parent_id int,
FOREIGN KEY(parent_id) REFERENCES item(id)
);
INSERT INTO item
(id, parent_id)
VALUES
(1, NULL),
(2, 1),
...
- 131
3
votes
2
answers
5k
views
Better defining 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 ...
- 591
3
votes
2
answers
948
views
Storing overridable hierarchical settings
I've searched the Internet and found the question Pattern(s) about hierarchical settings overwriting on Software Engineering SE. It exactly describes my need, the only difference is that I need to ...
- 229
3
votes
4
answers
4k
views
Faster alternative to scalar UDF with recursion to walk tree hierarchy?
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 ...
- 339
3
votes
2
answers
75
views
Nullable, dependent attributes
I'm designing a database for students. Computer science students here in Switzerland can decide between different specializations, e.g. application development, system engineering etc.
There are ...
- 133
3
votes
1
answer
294
views
Is a Self-defining Table of Domains Normalized?
I don't really know the official name of what I've made but it can't be the first time this has been made, so apologizes up front for not knowing every term.
My question, is a table as outlined below ...
- 271
3
votes
1
answer
3k
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 ...
- 211
3
votes
2
answers
9k
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 | ...
- 211
3
votes
2
answers
813
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 ...
- 131
3
votes
2
answers
4k
views
Get Nested/Hierarchical Data from SQL Table
I have a table relationship between 'Tag' and 'Task'. It is M:M. The relationship is mapped in the table 'TagTaskMapping'.
The TagAssignment table stores the relationship between a Tag and a date. So ...
- 1,414
3
votes
1
answer
5k
views
MySQL parent / child table not showing parent entries
I have a table in which categories are listed, and each product can belong to a parent category or not. There is only one level of possible depth.
CREATE TABLE `categories` (
`id` int(10) unsigned ...
- 155
3
votes
1
answer
324
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 associated ...
- 157
3
votes
1
answer
5k
views
Recursively find the path to all leaves descending from a given parent in a tree
I'm trying to write a query to identify the path to each furthest descendant of a particular parent node in a table of tree structures like:
0 1
| |
2 3
| |
4 5
...
- 155
3
votes
2
answers
5k
views
SQL Server CTE Bottom to Top Recursive with Where clause
I have an Employee Table with an EmployeeId, ManagerId and a Name field.
The goal is to make a recursive With fetching all the rows from an employee to the top manager (ManagerId is null).
I found ...
- 133
3
votes
1
answer
217
views
How to store hierarchical dimension for timeseries data
I have a requirement to store data about a load of Searches. Each time someone searches we want to record it for analyzing later on.
As part of the search the user can select one or more Categories ...
- 728