Questions tagged [hierarchy]
For issues relating to arranging items into ordered levels.
174
questions
1
vote
0
answers
73
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 ...
- 11
0
votes
0
answers
25
views
Hierarchical query to select `children` from `children`
I have the following table mytable where id is a unique identifier at a given r (resolution) and children is an array of unique id's of r + 1.
id
r
children[]
0
2
{0,2467972595799031808,...
- 263
0
votes
1
answer
26
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
...
- 103
0
votes
1
answer
1k
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
0
answers
55
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
792
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
0
votes
0
answers
166
views
Database for dynamic number of hierarchy levels
My client wants to support dozens of cities where each city has its own graph-like data structure to store location hierarchy, for example:
City 1: Region -> District -> Street -> Shop
City ...
- 11
3
votes
0
answers
330
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
0
votes
0
answers
168
views
Determine hierarchy with SELECT on same table twice
I need to model a hierarchial relationship in a mysql database. I found an excellent article, here, which describes the nested set model. I managed to adapt this work for my own requirements and it'...
- 113
1
vote
1
answer
87
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 ...
- 13
2
votes
1
answer
47
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/...
- 53
1
vote
1
answer
75
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 ...
- 53
2
votes
0
answers
85
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
426
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
126
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) ...
1
vote
3
answers
350
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 ...
- 13
3
votes
1
answer
2k
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
2
votes
1
answer
77
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 ...
- 307
3
votes
1
answer
91
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,417
1
vote
1
answer
189
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 ...
- 13
0
votes
1
answer
165
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 ...
- 103
1
vote
0
answers
332
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
48
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
9
votes
1
answer
491
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....
- 191
1
vote
2
answers
976
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 + ...
- 111
2
votes
3
answers
675
views
Foreign key reference to column that is no primary key
I know, foreign keys should uniquely identify rows and therefore reference a primary key.
However, consider my example: There are 4 pre-defined categories, and for some combinations of values for ...
- 121
0
votes
1
answer
604
views
Pattern for copying hierarchical data from one table to another?
Imagine a table with three columns: an ID primary key, a nullable ParentID referencing the ID column on that same table, and a TreeID to indicating the records that make up a particular tree. A basic ...
- 121
-1
votes
1
answer
513
views
SQL Variables inside SELECT query in a function
MariaDB 10.3.14
I have a function that generates the root id in a hierarchy:
The function is this:
CREATE FUNCTION `getRootId`(GivenID INT) RETURNS int(11)
DETERMINISTIC
BEGIN
DECLARE ch INT;
...
- 1
-1
votes
1
answer
136
views
MS SQL 2017: hierarchy tree - get quantity of last level components
There's a table of items with their components. Components depth vary i.e. some items may have one level of components other may have tens nested components. I need to get quantinty of last level ...
- 303
1
vote
1
answer
80
views
Generate hierarchical path
I have a classstructure table:
create table classstructure (classstructureid number(8,0), classificationid varchar2(25), parent number(8,0));
insert into classstructure(classstructureid, ...
- 1,417
1
vote
1
answer
202
views
Database Design - hierarchical structure
We are trying to redesign a database that currently holds thousands of parameters for different datatypes and currently has a flat data structure.
We generate XML files from the database, which our ...
- 11
0
votes
1
answer
20
views
Is there such a thing as an OLAP cube that can change the number of levels in a hierarchy without having to be rebuilt?
The current analytics solution that my team works with cannot handle changes in the structure of one of its hierarchies. If a new level or branch is added, then the entire domain must be first backed ...
- 149
0
votes
0
answers
57
views
How to retrieve all children of a parent record?
I have one MySQL table with this columns:
id
father
son
street
city
I want to obtain all descendants of a father recorded, how I can do this?
I tried this:
WITH RECURSIVE filhos AS (
select login, ...
0
votes
0
answers
1k
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 ...
2
votes
1
answer
218
views
How to select id of father from the child id recursively
I have in the field cod_categorizacao_pai the ID of the father, and I need to mount a select that just enter an ID and it will list the paternal IDs of this ID until it reaches the parent of all. How ...
2
votes
1
answer
2k
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 ...
- 143
1
vote
0
answers
330
views
Worked example of closure tables with from and thru dates
I work for a law enforcement agency, and have been asked to create a way of storing the chain of command in such a way that its structure can be viewed at any point in time, not only as it is now.
...
- 11
3
votes
2
answers
4k
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
4
votes
3
answers
74
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
0
votes
1
answer
410
views
Advantages of a hierarchical schema for private chat
This post claims that
All modern chatting interfaces, without exception, implement a
hierarchical and a not-chronological schema for chat.
It also links another post which gives an example ...
- 31
3
votes
2
answers
3k
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 ...
- 31
3
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 ...
- 441
1
vote
1
answer
1k
views
Oracle hierarchical query: aggregate over each node's descendants, give results in tree pre-order
Given hierarchical data, for each item I need to get the sum of a column over the sub-tree rooted at the item, and I need the results in pre-order.
Example: for the data
employee_id | manager_id | ...
- 233
2
votes
1
answer
2k
views
How make recursive json on mysql
my table looks like
CREATE TABLE `test_comments` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`parent_id` int(10) unsigned DEFAULT NULL,
`test_id` int(10) unsigned NOT NULL,
`user_id` int(...
- 21
1
vote
0
answers
488
views
How to get the hierarchy of particular employee using JPA spring boot?
I am trying to find a way to get the employee hierarchy in an organization using JPA and Spring boot.
The problem is similar to the following link
Similar Data Model
If A reports to B and B reports ...
- 11
2
votes
0
answers
53
views
Design for a child to have a single parent `A` OR `B` with FK cascade constraint?
I'm assuming this is not possible with PK and FK...
Let's say I have tables A, B, and C.
A and B are "parents", and B and C are "children"
A is the root
B can be the child of either A or B
C ...
- 121
1
vote
0
answers
82
views
Scalable way to manage aggregated statistics over time in hierarchy?
I'm using a closure table (Postgres through Django) to roll up stats through a hierarchical tree. When a node that is 10 layers deep has a value that increases, each parent's sum total of that value ...
- 11
-2
votes
2
answers
141
views
What's the best way to represent an event from a system in a chat?
Conversations
--------------
id
Messages
--------------
id
conversation_id
user_id
reply_to_id
content
created_at
I was looking at this question and I had a question: How to build a table for a ...
- 113
1
vote
0
answers
86
views
Nested set model in higher dimensions
The nested set structure is well-known and involves using left and right values to denote the relative heirarchy of nodes on one axis.
Is there a generalization of this structure to higher dimensions?...
- 11
9
votes
3
answers
24k
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 ...
- 523