Questions tagged [hierarchy]
For issues relating to arranging items into ordered levels.
182 questions
1
vote
1
answer
2k
views
How to return specific row number of a Hierachial query - Oracle
I have this query that works as expected. Within Oracle SQL Developer it returns ~19,500 rows. Unfortunately our current application limits our result to 10,000. I know I will need to run this query ...
- 11
0
votes
1
answer
99
views
Hierarchial, tree-like data model, which DB type? [closed]
I have the need for a database, I know what to store, however the question is what database to use and why. I have used relation SQL databases a lot in the past (up to a few years ago) and I know I ...
- 103
1
vote
1
answer
403
views
Convert hierarchy in an ordered list of rows
I tried to make a simple list that represents hierarchy of items.
Tags have simple structure like
[Id]
,[Name]
,[ParentTagId]
I try to make the follwing result table:
ID, ParentID, Name, Lev, ...
- 241
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
1
vote
3
answers
174
views
Avoid repeated traversing of hierarchical data when parsing a big tree frequently
I have a hierarchical/tree data structure in Oracle/RDBMS where I need to traverse the tree frequently. The tree is huge and therefore parsing it every time will not be efficient. A better way could ...
- 111
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
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
3
votes
1
answer
1k
views
How to maintain trees and their roots in SQLite
I have a (SQLite) database with a table representing nodes in a forest (set of trees):
create table "Node" (
"id" Text primary key,
"parent" Text references "Node" ("id") -- parent node
...
- 153
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
1
vote
0
answers
22
views
Model a tree that belongs to a row in another table
Given the tables "users" and "nodes" (which will contain an adjacency list tree) what is the best way to link these tables (each user has a tree).
Putting the column "user_id" in the "nodes" table ...
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
1
vote
1
answer
74
views
Animals hierarchy use-case: multiple farms, multiple species
Let's talk about an online service where goat/sheep/cattle/llama farmers would register and keep track of their animals.
One of the features of such a service is tracking parent/child relationship. ...
- 121
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
1
vote
0
answers
37
views
Heirarchial Database with Time Tracking
Working in Python/Django 1.8
I have a problem along the following lines:
Assembly Table
|Part
|Parent (FK to other Part)
i.e.:
Car
|--Tire
|--Bolt A
|--Bolt B
Journey Table
|Date
|...
- 111
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
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
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
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
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 ...
- 133
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 ...
- 121
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
3
votes
1
answer
44
views
How do hierarchical databases records link together?
If I had a hierarchical database containing 3 tables: course, students, grades
Course would be the root table and may have 10 course records in there.
Each course has its own selection of students, ...
- 31
2
votes
0
answers
90
views
How to represent multiple unique constraints across hierarchical tables?
My (shortened) table definition is:
CREATE TABLE bus
(
id integer NOT NULL PRIMARY KEY,
name character varying NOT NULL CHECK (name<>'')
);
CREATE TABLE network_node
(
id ...
- 121
1
vote
2
answers
182
views
How to break down RELATIONAL data into HIERARCHY data structure?
I have a query that returns the following dataset:
RELATIONid MAPid D1id D2id D3id
4999 4999 626 1250 7
5000 5000 626 1250 8
For the next step, I need to bind those ...
- 43
2
votes
1
answer
4k
views
Query for hierarchical RBAC scenario
I have a SQLite database with a hierarchical role-based access control (RBAC) data model.
In this model, users are granted permissions to perform certain actions against various resources either ...
- 131
1
vote
1
answer
3k
views
Select only rows that equal x but not x and y?
I'm having quite the challenge with selecting hierarchical data in MySQL although I feel the schema should be optimized for it since it's based on the WordPress database model.
I've got 4 tables:
...
- 143
0
votes
1
answer
611
views
How connect by is really working? How is it multiplying rows?
I know (on high abstract level) how hierarchies in Oracle works, but this one I do not understand.
When issued following query:
select rownum-1 as num from dual
I've got one row as result.
The ...
- 259
0
votes
2
answers
1k
views
Automatically set value on child rows when deleting parent row?
Description
We have a table and data below :
Version: PostgreSQL 9.3
Table name: tree_data (id int, code text, name text, parent_id int)
Primary key: id
Foreign key: parent_id (refer to id)
and ...
- 2,002
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
1
vote
1
answer
1k
views
How to manage many-to-many relationships when there is also a hierarchy among the many?
I have these entities – employee, task, phase, project – and these relationships:
project 1:m phase
phase 1:m task
employee m:m task, phase, project
The first two relationships form a ...
- 311
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
0
votes
1
answer
2k
views
How can I use Oracle's CONNECT BY PRIOR to only get specific revisions of hierarchical records?
I've got a table schema in Oracle 11 which is storing records that are linked together. The table schema is roughly like this
CREATE TABLE "SOMEDATA" (
"ID" INTEGER NOT NULL,
"ITEMNAME" ...
- 375
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
1
vote
2
answers
707
views
Hierarchical data in MongoDB?
I have a question regarding Hierarchical data.
Our app shows posts, that are structured inside parents. Here's how it goes:
prof->sprof->subject->post
(Each prof can contain multiple ...
- 113
0
votes
2
answers
238
views
Hierarcy Query Mysql [duplicate]
I have a table like this...
==========================================================================
UID || PARENT ID || SPONSOR ID
===============================================...
- 101
2
votes
1
answer
5k
views
Get products of category plus all child categories till leaf nodes
We have a hierarchy which looks this:
Now the problem is that most products are only connected with 1 category, which is most of the time the lowest level.
I am trying to get ALL products below a ...
- 520
1
vote
2
answers
507
views
How should I delete branch from adjacency list in SQL Server?
Given a basic acyclic adjacency list like this:
What is the most effective way to handle deletions of branches?
My current approach uses a recursive CTE to find all descendant nodes and their ...
- 1,693
0
votes
1
answer
5k
views
single mysql query that show a particular parent and child value [duplicate]
I am facing problem to show a particular parent child value and under all child value as a relationship. I have created single table tree and three column id, child and parent.
I created a tree ...
1
vote
0
answers
2k
views
How can I optimize a hierarchical query with multiple paths from child to root Oracle SQL?
I am trying to build a query that gives me the hierarchical history of events for a given child event_id. The events are related to each other by some from_id and to_id and the order is determined by ...
- 11
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
1
vote
2
answers
6k
views
Getting root node for a given leafs IDs
I got the following tables:
+-----------------+
| PRODUCTS |
+-----------------+
| #product_id |
| #company_id |
+-----------------+
| |
+---------------------+
...
- 13
0
votes
1
answer
312
views
queries for hierarchical data
This table is a questions and answers CMS and i want to know how data is fetched from these tables.
+------+----------+-------+
| id | parentid | type |
+------+----------+-------+
| 1 | 0 ...
- 15
1
vote
1
answer
773
views
hierarchical retreival of records in postgresql
I am using postgresql-9.2 . I have a table of products which stores the information about the products and its child table is content table , which contains the products linked to it.
PRODUCT MASTER ...
- 113
1
vote
1
answer
6k
views
HierarchyID: Get all descendants for a list of parents
I have a list of parent ids like this 100, 110, 120, 130 which is dynamic and can change. I want to get all descendants for specified parents in a single set. To get children for a single parent I ...
- 113
2
votes
1
answer
3k
views
How to handle multiple hierarchies in a data model?
In Sql Server, there is a datatype called HIERARCHYID which helps with hierarchies. In our environment, we have patient data that needs to be classified under multiple hierarchies.
Example 1: ...
- 707
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
0
votes
1
answer
2k
views
Oracle Hierarchical query: with two node attributes NodeId and NodeType
I have the following use case where I want to make use of hierarchical queries to get the desired result.
In my use case I have two types of node say 'A' and 'B'. So the unique identifier of node is ...
- 103
0
votes
2
answers
7k
views
How can I limit a hierarchical query to the point where there are multiple children?
This question is related to my previous question about hierarchical queries.
I have a hierarchical query in Oracle 11gR2 that returns something like this:
- Level 1
- Level 2
- Level 3
- ...
- 322
0
votes
1
answer
96
views
Table join inheritance
I create db with Person, Client, Employee, Manager, Developer. I'm interested what is the best way to put the data in tables. In which cases should programmer unite Client in table hierarchy with ...
- 143
0
votes
1
answer
112
views
Design for hierarchical embedded database
The goal is to construct a hierarchical database of Objects having the following format:
TypeA -> ObjectA [values, contains ObjectB*, ObjectC*, etc.]
TypeB -> ObjectB [values, contains ObjectC*,...
- 119