Skip to main content

Questions tagged [hierarchy]

For issues relating to arranging items into ordered levels.

Filter by
Sorted by
Tagged with
0 votes
1 answer
2k views

While Loop - Parent/Child Tree

I'm trying to recursively loop through and return all child_id's that have the root element of 9. The structure: ->9 ->->8 ->->->17 ->->22 ->->->11 Parent Child ...
2 votes
1 answer
586 views

SSAS Cube for tracking changes in parent child relationship over time

I would like to build an SSAS cube which tracks how objects in a graph who's edges represent a "belongs to" relationship change over time (daily). There are two components to the change: which object ...
0 votes
2 answers
5k views

System/database design for comments/replies and upvotes at scale

Recently I started discovering a topic around "How to design db schema for storing structures similar to Instagram/Facebook/Reddit comments?". After extensive research, I was able to find a ...
0 votes
1 answer
57 views

What is the best way in Postgres to inherit values from parent row recursively if not provided?

Is there a pattern in SQL where a child row inherits empty (null?) values from a parent? E.g., given the following 'chickens' table (sorry): id parent_id name flys noise weight egg_color ...
0 votes
0 answers
33 views

Recursive CTE hiearchy snowflake -- join/ expand outward (columns) instead of rows?

I'm doing a dependency mapping explosion (like parts explosion). I did a typical recursive CTE with a union all. It looks like with CTE as ( select abc from myTable where start_point = X union all ...
0 votes
1 answer
2k views

Sql subquery for nested parent child data

I have two tables , on parent table (categories ) and child table (features).. I have this query select c.* , (select GROUP_CONCAT(f.featuresId , f.featuresTitle ) from ...
0 votes
1 answer
69 views

SQL Pattern to get "root/ start" of uneven hierarchy dataset

I feel this is a common problem and I've seen it in some SQL challenges even but for the life of me, cannot think clearly about a solution. Say you have an uneven hierarchy. Elements that belong to ...
0 votes
0 answers
33 views

How can I duplicate adjacency lists without breaking parent-child relationships?

I would like to duplicate hierarchical structures, modelled as adjacency lists, in my database. The following tables and records allow a minimal reproduction: CREATE TABLE item_groups ( id serial ...
0 votes
0 answers
36 views

Finding tree contours using nested set model

Is there a query or algorithm that will enable me to find the left and right contours for general hierarchical trees and subtrees described using the nested sets model? I have described tree ...
0 votes
1 answer
287 views

What is the most straightforward way to create subtypes and supertypes at the same time?

I hope this isn't too basic a question, but I am hoping that the DB engineers here will help me out. Do relational DBs usually have a straightforward way of doing the following?: Create a supertable ...
0 votes
1 answer
23 views

Database for inheritance hierarchy queries

I'm looking for a way to store hierarchical data, in a way that will facilitate "inheritance" queries that will coalesce upwards in the hierarchy. Simple example: Level 1, property X = 123 -...
0 votes
1 answer
61 views

Normalisation with an Underlying Hierarchy

I'm designing a new database schema, and every time I do, I like to check my assumptions on normalisation. Something I haven't been able to find a definitive answer for here or by searching the web is ...
-1 votes
1 answer
24 views

Modeling combinations (options and dependencies)

How would you modelize the following data? I have a solution I wrote in a separate answer. Don't forget to read it. Context It's for a website. Speed is of the essence. Data You have objects that are ...
1 vote
0 answers
182 views

How to write query to link complex Parent-Child relationships

We have a table that has a DefectID field, and a PreviousDefectID field, in order to link defect records to each other as they change through time. I need to link these fields together by creating a ...
-1 votes
1 answer
95 views

Creating a hierarchy from text

I'm trying to create a hierarchy from a text field I have in my table. Visually it is relatively easy to see who is parent and child. For example: (see a screenshot of the table results) 1M -----1M5 --...
1 vote
0 answers
223 views

MySQL: Hierarchical Organization of Tables

There are several SE Q&As regarding how to hierarchically organize tables in MySQL: How to use a naming convention for large databases? Asked 12 years, 11 months ago How can I organize a glut of ...
0 votes
1 answer
31 views

sql query for category heirarchy validations

I need to add validation on category creation. CASE 1: parentId should be valid if supplied CASE 2: name of sibling could not be duplicated I have this table: (categories) id | parentId | name ...
0 votes
3 answers
356 views

Information sources for multiple hierachy trees in a single table

I need some quality sources - books, websites etc. - to educate myself about putting multiple hierarchy trees in a single table using SQL. I'm looking for some good theoretical and practical ...
0 votes
0 answers
108 views

Database design for a forum

I am trying to build a forum kind of like reddit. Discussions can have comments and people can comment on comments. My solution for such a hierarchical database is to use a closure table for my ...
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), ...
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 ...
3 votes
0 answers
605 views

How to map child (leaf-level) nodes to specific parents with/without ltree for aggregations?

What would be the best way to aggregate across hierarchies? I am using Postgres and have the following 3 tables (folder, folder_hierarchies, and work) - folder Column Type Description folder_id (pk)...
1 vote
1 answer
178 views

Query hierarchical file permissions

I have a table representing some files with their paths and another table with file sharings by user and files. Below is Table files, simplified. Example: I have a directory A (path /A) containing ...
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 ...
2 votes
1 answer
54 views

Is there any harm in storing other rows' int PKs in a hierarchyID instead of an arbitrary number?

I'm looking at common uses of hierarchyID, and most examples use some sort of arbitrary numbering for the HID nodes: HID, Name /, World /1/, USA /1/1/, Texas /1/1/1/, Houston /1/1/2/, San Antonio /1/2/...
1 vote
1 answer
99 views

What facilities of SQLServer would help achieve a goal of tracking a hierarchy over time?

I've a requirement to create a system that tracks what is essentially a hierarchy of containers over a period of time, modelling a supply chain. Everything in the chain is a container, so the process ...
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 ...
1 vote
1 answer
2k views

Social network comments and posts model in PostgreSQL 9.6

I'm trying to figure out a data structure for migrating from MongoDB to PostgreSQL. Within my posts table in Postgres, I have a JSONB[] array holding comments for the post. The thing is that each ...
2 votes
0 answers
121 views

MS-SQL and HierarchyID

I have been using HierarchyID for the last 2 years in some of my tables for obvious purposes And I have kept a Column (int) for the last node number for example if I have these rows so the int column ...
2 votes
0 answers
584 views

Postgres ltree: Get all accessable resources by groups and sub groups

Have following Data scheme: Roles: Each role has either a resource assigned or a resource_group. A user wich has a role on a group has also access to all resources that are within the group or within ...
0 votes
1 answer
165 views

MSSQL Merge 3 hierarchy tables

I want to merge 3 hierarchy tables into one table. The 3 tables: CREATE TABLE dbo.tbl1 ( key1 varchar(50) NULL, key2 varchar(50) NULL ) ON PRIMARY CREATE TABLE dbo.tbl2 ( key2 varchar(50) ...
65 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 ...
1 vote
3 answers
675 views

Constraint to prevent immediate loop in hierarchy

I have a companies table: CREATE TABLE companies ( id bigserial, name varchar(255) NOT NULL PRIMARY KEY (id) ) I want companies to have parents and childs. A company might have many parent and ...
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 ...
9 votes
1 answer
651 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....
9 votes
3 answers
424 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 ...
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 ...
3 votes
3 answers
4k 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`), ...
2 votes
1 answer
116 views

Org chart CTE with exact index but plan shows expensive sort node

I've got a moderately large table (148 million rows) that's got pretty common org chart attributes to it. Names are changed, but it's the same idea. I was thinking of adding a hierarchyid column to ...
1 vote
1 answer
665 views

SQL relationship for nested groups

I'm working on a creating a database schema for a legacy app that currently saves its data to XML files. I'm having trouble representing this relationship: Task Group 1 contains |-- Simple Task 1 |--...
0 votes
1 answer
273 views

Procedure for tree view [duplicate]

I trying to fetch data form database which is have 3 column unique_id, parent_id & node_name, but this query (Given in below) is return only two rows. Condition: every parent has two child. DROP ...
3 votes
1 answer
198 views

Ordering CONNECT BY siblings

I have an hierarchical query that uses CONNECT BY: select wonum, parent, classification, classstructureid, division, worktype, status, glaccount, fircode, ...
1 vote
1 answer
289 views

Relationship design for areas, managers and general manager

Given some specifications I need to design a database for an organization. One of the parts has to do with areas. In the organization there are different areas, each area has a boss, and there is also ...
0 votes
1 answer
328 views

How to improve poor performance with two joined CTE expressions on hierarchical data?

I have a table of hierarchical data where I want to select entries that, 1) only include matches that are descendants of a given parent, and 2) returns the full hierarchical path of the matched ...
3 votes
2 answers
4k views

Parent Child Relation in same Table . Select Query to get only parent records and Records not comes under parent child relation

I have a table with Parent-Child Relation in the same Table. Some Sample Data id │ name │ parent │ is_active ════╪══════════════════════╪════════╪═══════════ 1 │ Company A ...
1 vote
0 answers
943 views

Recursively list dependencies / references of a stored procedure

I'm trying to import a subset of our production dump with impdp for our local development database running on Oracle XE. To find out what other objects I have to import for a particular stored ...
0 votes
1 answer
69 views

Aggregating nested set models with field constraints

I have a table built on the nested set model that basically records transactions of different natures (defined by the field idx): pk lft rgt idx value 1 1 30 2783 20402.710000 2 ...
1 vote
2 answers
2k views

Hierarchical data using closure table, how to ensure that a column is unique within the parent node?

I store hierarchical data in a Node-table, combined with a closure table (NodeClosure). Each node has a column "name" that should be unique among the sibling nodes (link to parent-Node + ...
1 vote
2 answers
950 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 ...
3 votes
1 answer
315 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 ...