Questions tagged [hierarchy]
For issues relating to arranging items into ordered levels.
182 questions
0
votes
2
answers
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 ...
CommunityBot
- 1
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 ...
CommunityBot
- 1
0
votes
2
answers
48
views
Schema to model user authorization in hierarchical data
I'm trying to add the ability for a user to be authorized with a given role on a contract/site/experiment.
More specifically, I want the ability for a user to have role A on a contract but at the ...
- 16.6k
2
votes
1
answer
600
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 ...
CommunityBot
- 1
2
votes
1
answer
3k
views
How to query all entities associated with all children (recursive) of a node in SQL?
This question is about SQL in general. Answering specifically for MySQL would be helpful but not necessary.
Ok, I’m having trouble putting this into words… so bear with me.
Say I have a tree of ...
- 2,116
0
votes
0
answers
72
views
Modeling hierarchical data for multiple entities, inherited properties, and varying hierarchy
I am trying to model hierarchical data using a RDBMS but can't quite find the right solution. We are capturing information about storage levels in a warehouse system. There are 8 possible storage ...
- 1
0
votes
2
answers
6k
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 ...
- 3,692
0
votes
1
answer
183
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
...
- 109
0
votes
0
answers
173
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
...
- 1,709
0
votes
1
answer
155
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 ...
- 39.5k
0
votes
1
answer
500
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 ...
- 4,386
0
votes
1
answer
27
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
-...
- 1,159
0
votes
1
answer
86
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,646
-1
votes
1
answer
27
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,762
1
vote
0
answers
214
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 ...
- 14.5k
-1
votes
1
answer
130
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
232
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 ...
- 1,762
0
votes
1
answer
37
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
...
- 47.6k
0
votes
3
answers
357
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
126
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 ...
- 1
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),
...
- 4,463
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 ...
- 19
3
votes
0
answers
621
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)...
- 131
1
vote
1
answer
197
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 ...
- 182k
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 ...
- 777
2
votes
1
answer
59
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/...
- 9,480
1
vote
1
answer
104
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 ...
- 39.5k
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 ...
- 64.7k
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 ...
- 64.7k
2
votes
0
answers
139
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 ...
- 119
2
votes
0
answers
626
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 ...
- 21
0
votes
1
answer
174
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) ...
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 ...
- 113
1
vote
3
answers
773
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 ...
- 182k
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
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....
CommunityBot
- 1
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 ...
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
...
- 20.3k
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`),
...
- 23.9k
2
votes
1
answer
124
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 ...
- 255
1
vote
1
answer
675
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
|--...
- 25.1k
0
votes
1
answer
293
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 ...
- 184k
3
votes
1
answer
252
views
Ordering CONNECT BY siblings
I have an hierarchical query that uses CONNECT BY:
select
wonum,
parent,
classification,
classstructureid,
division,
worktype,
status,
glaccount,
fircode,
...
- 23
1
vote
1
answer
312
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 ...
- 39.5k
0
votes
1
answer
357
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 ...
- 23.6k
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 ...
CommunityBot
- 1
1
vote
0
answers
1k
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 ...
- 121
0
votes
1
answer
76
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 ...
- 123
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 + ...
- 23.6k
1
vote
2
answers
959
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 ...
- 182k