Questions tagged [hierarchy]
For issues relating to arranging items into ordered levels.
182 questions
1
vote
1
answer
2k
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
561
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
56
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
106
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
165
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
107
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
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
2
votes
1
answer
430
views
Correct way to join row in parent table to row in one of several children tables
I have a parent table product, and several children tables like phone, tablet, smartwatch etc. The parent and children have 1-1 relationship. This means that for every row in parent table, there will ...
- 121
1
vote
1
answer
110
views
How to add foreign key in a table where column value can come from two different tables?
I have three tables
create table main_comments (
id serial primary key,
comment text not null
);
create table sub_comments (
id serial primary key,
main_id integer not null,
comment ...
- 113
3
votes
2
answers
935
views
Get all descendants from 3 hierarchical tables
I have the following tables within a database:
+-------------+ +-------------+-------------+ +---------------------------+
| project1 | | project2 + | project3 ...
user148246
0
votes
1
answer
532
views
Bypass truncated data type error in `CREATE TABLE AS`
I am attempting to recreate the code from this excellent tutorial in MariaDB 10.2 to try and build nested sets from an existing adjacency list. (The goal being to ingest changes to the adjacency list ...
- 510
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
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 ...
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
1
vote
1
answer
224
views
Select top-most folder in a hierarchy by given criteria in MySQL
I'm using MySQL (5.7.20) to store a folder hierarchy. Each folder is stored together with all direct and indirect parents including the distance. As an example, the following folders
A
+- B
+ C
...
- 113
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 ...
- 1
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
0
votes
1
answer
2k
views
Design: Postgres, Parent to Child / Child to Parent / Child to Child | JSONB
I am very new to database design. I am hoping for suggestion or solutions for a feature I am working.
Note: We are majorly using JSONB type in Postgres
Feature:
I have a orders table which would ...
- 103
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
0
votes
1
answer
692
views
Why does Oracle yield multiple levels per row in this hierarchical query?
I'm trying to reproduce the first simple example from this tutorial.
However, I don't understand why I get so many rows back. I would expect that 1 reports to 2 only at level 1 and not at level 2 as ...
- 103
2
votes
2
answers
9k
views
How to organize binary tree in database? [closed]
Here is the situation, I need to organize some users like an binary tree to explain organization workflow, but organization can change.
Here is how its look like:
1
/ \
2 ...
- 241
2
votes
0
answers
679
views
Auto-update hierarchyid of multiple records in INSTEAD OF UPDATE trigger
I am working with an INSTEAD OF UPDATE trigger, where I have to calculate / re-calculate the hierarchyid of a row based on another column value. Thus far, I have it working. The problem that I run ...
0
votes
1
answer
373
views
Recursive Seller Account with Balance
I need to build a database for managing Short Messaging Service (SMS).
In this I have 2 types of users
Reseller
Must have starting SMS balance for example: 1,000 - credit table
He can sell ...
- 101
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
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 ...
- 101
0
votes
1
answer
578
views
How to retrieve all the rows where primary key value found in the other column in the row in MySql
Following is MySql Table in the snapshot:
column sno could be found in the other rows in column p_sno. i.e.
sno = 5 linked through p_sno = 5 with sno = 6 (two rows)
sno = 8 linked through ...
- 105
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
3
votes
2
answers
14k
views
How to flatten and pivot a normalised hierarchy table
I have an OrgChart table that looks something like this:
PositionCode PositionName ParentCode Level
AA0001 CEO 0
AA0002 CFO AA0001 1
AA0003 ...
- 31
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 ...
Duxducis
-1
votes
3
answers
2k
views
Create parent node and parent id [closed]
I have a table which contains id, nodeid, nodevalue, and nodelevel columns.
Now I want to create new temp column ParentnodeId.
I tried using CTE but not getting the correct result.
Id Nodeid ...
- 3
2
votes
1
answer
2k
views
Unflatten hierarchical data in SQL Server
I have some tables in the staging area of a data warehouse that I'm filling with data from some flat, comma-delimited text extracts from another system. When the data comes in the hierarchy of parents ...
- 2,087
1
vote
1
answer
242
views
Which postgres design for a nested resource that can link to the same type of resource
I have this kind of data that I need to store in postgres (ruby object).
#<InitRes:0x007f8ec3734c78
@kind="RestrictionsSchema",
@restrictions=
[#<ParsedRes:0x007f8ec60ed218
@...
- 651
1
vote
0
answers
2k
views
Joining recursive table in PostgreSQL
Situation
I have two simple tables, one called "node_relations" and one called "node_information". The table "node_relations" represents a simple parent-child hierarchy, having only two columns "id" ...
- 119
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
|--...
- 113
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
1
vote
0
answers
157
views
SQL select for a specific node all subnodes
I'm using mySQL on phpMyAdmin, my table is
| code | name | parent |
|------|------|--------|
| 10 |node10| 5 |
| 5 |node05| 2 |
where 5 is the parent of 10 (which is also a node), I ...
- 119
0
votes
1
answer
214
views
Join mysql tables to find siblings and get their notifications
i have two mysql tabes one for company and one for notifications.
Company
+-----------------------+
| ID |parentID|vaue |
|1 |0 |22020 |
|2 |1 |23423 |
|3 |2 |22222 ...
1
vote
1
answer
1k
views
How to get last level of a hierarchical list, group by some field
Given the schema:
create table containers (
id int primary key,
name text,
product_id int,
lot int,
qty int,
parent_id int);
create table orders_items (
id int,
...
- 23.9k
1
vote
1
answer
10k
views
Build an Oracle query to get parent and child records
Table structure as below
col1| col2 | col3|
1 | empty|empty|
2 | 1 | Xyz |
3 | 1 | abc |
4 | 2 | abc |
Note Col2 is deciding parent record for child record.
Looking to get result ...
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
1
vote
0
answers
74
views
Shared 'directories' using closure tables
As part of an application I'm working on, users need to be able to store files in directories, and share certain directories for collaboration.
After doing some initial research, I decided to ...
- 111
0
votes
1
answer
3k
views
Oracle 11g hierarchical queries with limit, offset and order by to achieve pagination on the root level
I'm using Oracle 11g (unfortunately not 12c, i.e. OFFSET n ROWS FETCH NEXT m ROWS ONLY is not available) and I'm trying to achieve pagination on a hierarchical query.
The aim is to sort the root ...
- 111
2
votes
2
answers
2k
views
How to detect a circular reference in a hierarchy table?
I have table like this:
CREATE TABLE MyTable (
Id INT NOT NULL PRIMARY KEY,
ParentId INT NOT NULL
);
ALTER TABLE MyTable ADD FOREIGN KEY (ParentId) REFERENCES MyTable(Id);
select * from ...
- 21
0
votes
1
answer
818
views
Best way to implement Hierarchical data in SQL Server with multiple parents
How can I implement Hierarchical data where a child has two or more parents?
Something like this
Parent ID Child ID
1 2
1 3
3,2 4
4,1 5
- 101
1
vote
0
answers
61
views
The efficiency of an hierarchical table and query
I have this hierarchical table that will end up containing 5 or 600 unique names, many of which will be assigned to multiple parents, such as "Hunt" below...
+-------------+----------------------+----...
- 81
3
votes
1
answer
98
views
Hierarchical Storage and Query
I am editing and providing new info to this question because i've found what I need, just have a couple of questions about efficiency.
Now, I have this table:
category
--------------------------
...
- 81
1
vote
0
answers
333
views
Getting count of all children and if any child contains user id in nested hierarchy
I am trying to create a newsfeed for posts and comments. The user should see:
1. A latest post in liked group or by a followed user
2. A post with the latest comment (n-th level) in liked group or ...
- 111
1
vote
2
answers
66
views
How to correctly create a table that has multiple columns self reference another row?
EmpNo |Designation | ReportsTo | Room
10 | Director | NULL | 1
20 | Senior Manager | 10 | 2
30 | Typist | 20 | (THIS GUY REFERS TO empno 10 room)
40 | ...
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