Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [tree]

The tag has no usage guidance.

0
votes
0answers
11 views

How does query parameters work under the hood?

I thought about how can it work under the hood and I came with an idea Db engine receives query with parameters and also defined values of those parameters SQL is being parsed into syntax tree like ...
1
vote
1answer
55 views

Hierarchical / tree data - which type of database to use & design?

For querying (and writing to) a hierarchical structure (like a folder containing files and other folders, up to n levels deep, where n is pretty small, probably around 10, and there would be lots of ...
1
vote
2answers
51 views

Joining two ltree based tree tables recursively

I have two tables, roles and permissions, both of which use ltree for maintaining a tree structure. I also have a pivot table, roles_permissions, which serves to connect the two tables. How can I join ...
5
votes
2answers
183 views

PostgreSQL - retrieve all IDs in a tree for a given subnode

I have a non-binary tree of customer, and I need to obtain all the IDs in a tree for the given node. The table is very simple, just an join table with a parent id and child id. This is a ...
0
votes
0answers
47 views

Using SP-Gist or Gist(cube) index to implement kdtree for high dimensional data in Postgresql

I plan to implement kd-tree for high dimensional (100-200 columns) data in Postgresql but I don't know how to start. After I read the documents of SP-Gist and Gist index, I was confused. 1.Sp-GiST ...
0
votes
0answers
24 views

Example of Tree Indexing Structures

This presentation goes in depth into some indexing structures but doesn't go into XML indexes (or tree indexes). By tree indexes I don't mean B-trees and the like, which are used for indexing single-...
2
votes
1answer
35 views

How to Store Trees on Disk

I am wondering how to store trees on a physical disk, for perhaps a tree-oriented database like an XML database. Wikipedia states about XML databases: ...custom optimized data structures are used ...
1
vote
0answers
40 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 ...
6
votes
0answers
772 views

Working on a decision tree database design

I'm doing the conceptual model for a Decision tree database. The goal is store any Decision tree. I have four entity types: DecisionTree, Node, Branch and Leaf. Each Decision tree is composed of ...
1
vote
0answers
47 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?...
0
votes
1answer
52 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 ...
0
votes
1answer
248 views

How can I get performance using PostgreSQL CTE recursive?

I did a tree structure using id and parent_id in the same table. For query's I'm using CTE provide by PostgreSQL, but it's spend so much time to do the joins with recursive results. For example, by ...
2
votes
1answer
583 views

Recursive self relational via join column

Having this specific simple many to many self referential structure An item owns other items through the joins table; trying to retrieve the whole tree structure as json while querying a given ite. ...
2
votes
4answers
83 views

Representing ownership of heirarchical tree structure

In an application I'm developing, I have (among others) two structures: Users and Folders. CREATE TABLE users ( id SERIAL PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE folders ( id ...
0
votes
1answer
87 views

Binary tree “get open list” SQL query

I have a node table that looks like this, plus it has an inserted_at column: id | parent_id ----------+----------- 1 | nil 2 | 1 3 | 1 4 | 2 5 | 2 ...
5
votes
1answer
821 views

What would be the maximum number of records that can be indexed with a three levels B-tree ? B+ tree?

I am learning dynamic tree-structure organizations and how to design databases. Consider a DBMS with the following characteristics : file pages with size 2048 bytes pointers of 12 bytes page header ...
1
vote
1answer
220 views

How to create data tree in MS Access

I am trying to build a database in which I am going to need to have a tree to keep track of cars. Unfortunately, I don't always know what level of detail I am going to have, and the level that I do ...
2
votes
0answers
278 views

Retrieve all product from one category (tree model)

I'm using MySQL, have categories in a tree model. One product belongs to only one (leaf) category. What I want to accomplish is, given one category (any, could or not be a leaf or a root), return ...
5
votes
2answers
3k views

How to best store a directory tree in a database?

I want to represent my directory structure in some format (currently I'm just using JSON.) This is how a sample JSON might look. For those curious it was generated using unix tree command: tree /path/...
0
votes
1answer
230 views

Can InnoDB use a clustered index as a covering index?

The scenario I have in mind is a query like: SELECT id FROM table WHERE id = ? or equivalently, SELECT 1 FROM table WHERE id = ? So here's some background: In a B+ tree (which InnoDB uses as ...
0
votes
1answer
90 views

DB schema for storing the process tree of linux

I search a db schema to store the process tree of linux every half hour. Platform: PostgreSQL on Linux. I am unsure where to split all the data into columns or to use a json column. Which one is ...
4
votes
1answer
201 views

B Tree and B+Tree differences

I'm studying B+ Tree and B Tree and I would like to understand two things about it, if someone can clarify it to me I would appreciate: Why can I store more search keys on an B+ Tree Index? My guess ...
4
votes
1answer
830 views

For each category, find the count of foreign-key items in all child categories using a PostgreSQL Recursive CTE

I have a typical tree structure stored as an adjacency list in PostgreSQL 9.4: gear_category ( id INTEGER PRIMARY KEY, name TEXT, parent_id INTEGER ); As well as a list of items attached ...
0
votes
1answer
115 views

What's the best method to implement a very large tree structure in a database?

I am writing a back-end program for a trading brokerage with 1000+ clients, some of which make as many as 500+ trades a day. My C# program keeps track of everything in the following tree method: The ...
4
votes
2answers
653 views

Lineage tree database design [duplicate]

As the title says, I'm building a cannabis strain genetics/lineage database. If you didn't know; one cannabis strain may be an original strain (no parents) or it could be the result of many crosses (...
3
votes
2answers
4k views

Best design for a tree-like structure database

I'm designing a database in MS Access which is used for tracking the locations of a variety of chemical solutions, made in batches. Each batch can be split (aliquoted) practically indefinitely. That ...
-3
votes
1answer
723 views

MySql - Get all child categories until not exists

Based on this answer, can you please tell me what's wrong in the next code ? I'm trying to get all child categories. The example that I try it is: DELIMITER $$ DROP FUNCTION IF EXISTS `...
2
votes
1answer
355 views

“No Loop” Constraint for hierarchical data in postgresql

I've got table like this in postgres: id primary key parent_id foreign key on id some data columns I need no a constrain which prevents cycles on the parent relations. There might be more tree in ...
1
vote
1answer
969 views

Implement a Parent-Level Count in Binary Search Tree

Based on this question, have any way to implement a parent level count? Find highest level of a hierarchical field: with vs without CTEs Example table: +----+-----------+ | id | parent_id | +----+-...
2
votes
1answer
2k views

Summarize from unlimited level depth parent-child in mysql

I have 2 table master coa and coa transaction in MySQL like this, master : id | coa | id_parent | ----+------+-----------+ 1 | 1 | NULL | 2 | 11 | 1 | 3 | 111 | 2 ...
2
votes
1answer
727 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 ...
2
votes
1answer
11k views

Recursive Query in MySQL using stored proceedure and CURSOR

I'm expanding our user/group system to allow for dynamic groups which are made up of other groups members. I have three tables users, groups, and relationships. For simplicity sake, lets say users ...
2
votes
0answers
193 views

Building a tree in Apache Derby 10.10

I know this question has been asked probably a million times, so please forgive me asking it again. I am not a DBA and have very little knowledge of SQL and Derby, but need some kind of a solution as ...
7
votes
3answers
13k views

COUNT of rows with parent id equal to the row we have with a parent id of 0 in one query

I'd like to do the following in one query using MySQL: grab a row that has a parent_id of 0 grab a count of all the rows that have a parent_id of the row that we grabbed which has a parent_id of 0 ...
2
votes
1answer
162 views

How to select nodes where all children is satisfied?

I have a tree structure of light bulbs. I want to turn on all the light bulbs starting from the leafs of the tree. A light bulb cannot be turned on unless all its immediate children are turned on. ...
1
vote
2answers
1k views

Performing SELECT on EACH ROW in CTE or Nested QUERY?

This is a problem in PostgreSQL I have a table which stores the tree of users; +------+---------+ | id | parent | |------+---------| | 1 | 0 | |------|---------| ...
5
votes
1answer
887 views

Modelling Hierarchical attributes

I am trying to figure out the data model for a retailer. The retailer has several stores across the country and they are modeled using the following hierarchy: Channel -> Zone -> City -> ...
3
votes
1answer
13k views

Loop through Self JOIN on table until the operand column is NULL completely

For the problem I have a single table which store a Member's ID and His Referee's ID. This is something like a Multi-Level-Marketing plan where you join a company for some tasks and then someone join ...
-1
votes
2answers
328 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
1answer
495 views

Combining a tree structured Group model with its HABTM User model in one VIEW

I have a tree structured model Group which has the columns id, parent_id, name, lft and rght. I also have a model User which has the columns id, name and password. Group and User have a Has-and-...
6
votes
1answer
5k views

PostgreSQL tree structure and recursive CTE optimization

I'm trying to represent a tree structure in PostgreSQL (8.4) to be able to query the path from the root to a given node or to find all the nodes within a sub-branch. Here is a test table: CREATE ...
2
votes
1answer
2k views

How to traverse a tree in SQL? Sybase edition [duplicate]

Possible Duplicate: Traversing tree-like data in a relational database using SQL Whats the best way to traverse a tree in sybase? Basically, what I want is to gather all the decedents of a ...
13
votes
2answers
2k views

How to structure a model to properly and efficiently represent tree-like data on relational databases?

Based on Traversing tree-like data in a relational database using SQL question, I would like to know how the way regularly used to describe tree-like data on relational databases considering physical ...
16
votes
4answers
6k 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 ...