Questions tagged [hierarchical-data]

Hierarchical data reflects a set of parent-child relationships. These can be found in a genealogy, a taxonomy, a list of requirements for parts assembly, and innumerably other instances. Methods for dealing with hierarchical data are often essential for data management and analysis.

Hierarchical data reflects a set of parent-child relationships. As Materials Resource Planning was developed by manufacturers in the 1950s, hierarchical data management emerged as a theoretical and practical discipline. A manufacturer uses the term Bill of Materials (BOM) for the set of items required to assemble a parent item; the term BOM was adopted by mathematicians and software developers, and it is used today in both realms.

Hierarchical data is ubiquitous and has called forth some of the most important and complex work of database management. It has also propelled object-oriented programming.

The key design element is recursion. Imagine starting at the trunk of a tree and visiting every leaf; this theoretical exercise would require an enormously large number of decision points occurring in a nested or recursive pattern. Requirements for this analysis are remarkably varied and have produced entire sub-disciplines of database design.

2030 questions
1580
votes
8 answers

What are the options for storing hierarchical data in a relational database?

Good Overviews Generally speaking, you're making a decision between fast read times (for example, nested set) or fast write times (adjacency list). Usually, you end up with a combination of the options below that best fit your needs. The following…
orangepips
  • 9,891
  • 6
  • 33
  • 57
571
votes
15 answers

What is the most efficient/elegant way to parse a flat table into a tree?

Assume you have a flat table that stores an ordered tree hierarchy: Id Name ParentId Order 1 'Node 1' 0 10 2 'Node 1.1' 1 10 3 'Node 2' 0 20 4 'Node 1.1.1' 2 10 5 …
Tomalak
  • 332,285
  • 67
  • 532
  • 628
433
votes
16 answers

How to create a MySQL hierarchical recursive query?

I have a MySQL table which is as follows: id name parent_id 19 category1 0 20 category2 19 21 category3 20 22 category4 21 ... ... ... Now, I want to have a single MySQL query to which I simply supply the id [for instance say…
Tarun Parswani
  • 4,565
  • 3
  • 13
  • 13
199
votes
22 answers

How to efficiently build a tree from a flat structure?

I have a bunch of objects in a flat structure. These objects have an ID and a ParentID property so they can be arranged in trees. They are in no particular order. Each ParentID property does not necessarily matches with an ID in the structure.…
Costo
  • 5,940
  • 8
  • 33
  • 35
119
votes
7 answers

Simplest way to do a recursive self-join?

What is the simplest way of doing a recursive self-join in SQL Server? PersonID | Initials | ParentID 1 CJ NULL 2 EB 1 3 MB 1 4 SW 2 5 YT NULL 6 IS …
Chris
  • 3,081
  • 3
  • 32
  • 37
109
votes
11 answers

Convert a series of parent-child relationships into a hierarchical tree?

I have a bunch of name-parentname pairs, that I'd like to turn into as few heirarchical tree structures as possible. So for example, these could be the pairings: Child : Parent H : G F : G G : D E : D A : E B : C C : E …
Eric
  • 95,302
  • 53
  • 242
  • 374
85
votes
8 answers

Resampling Within a Pandas MultiIndex

I have some hierarchical data which bottoms out into time series data which looks something like this: df = pandas.DataFrame( {'value_a': values_a, 'value_b': values_b}, index=[states, cities, dates]) df.index.names = ['State', 'City',…
78
votes
13 answers

What type of NoSQL database is best suited to store hierarchical data?

What type of NoSQL database is best suited to store hierarchical data? Say for example I want to store posts of a forum with a tree structure: original post + re: original post + re: original post + re2: original post + re3: original post …
deamon
  • 89,107
  • 111
  • 320
  • 448
66
votes
9 answers

How to represent a data tree in SQL?

I'm writing a data tree structure that is combined from a Tree and a TreeNode. Tree will contain the root and the top level actions on the data. I'm using a UI library to present the tree in a windows form where I can bind the tree to the…
Avi Harush
  • 989
  • 2
  • 10
  • 15
58
votes
9 answers

Is it possible to query a tree structure table in MySQL in a single query, to any depth?

I'm thinking the answer is no, but I'd love it it anybody had any insight into how to crawl a tree structure to any depth in SQL (MySQL), but with a single query More specifically, given a tree structured table (id, data, data, parent_id), and one…
Cameron Booth
  • 6,882
  • 5
  • 28
  • 22
55
votes
3 answers

Backbone with a tree view widget

I'm evaluating the Backbone javascript framework for use in a project that will display a hierarchical model in a tree view widget (think the Windows file browser). I love how Backbone thinks about the world. However, there's a lot of coding…
Dean Moses
  • 2,372
  • 2
  • 24
  • 36
54
votes
4 answers

Is it a good idea to use MySQL and Neo4j together?

I will make an application with a lot of similar items (millions), and I would like to store them in a MySQL database, because I would like to do a lot of statistics and search on specific values for specific columns. But at the same time, I will…
Jonas
  • 121,568
  • 97
  • 310
  • 388
53
votes
5 answers

basic recursive query on sqlite3?

I have a simple sqlite3 table that looks like this: Table: Part Part SuperPart wk0Z wk00 wk06 wk02 wk07 wk02 eZ01 eZ00 eZ02 eZ00 eZ03 eZ01 eZ04 eZ01 I need to run a recursive query to find all the pairs of a given SuperPart…
Eric
  • 3,301
  • 4
  • 33
  • 39
53
votes
12 answers

Nice & universal way to convert List of items to Tree

I have list of categories: ╔════╦═════════════╦═════════════╗ ║ Id ║ Name ║ Parent_id ║ ╠════╬═════════════╬═════════════╣ ║ 1 ║ Sports ║ 0 ║ ║ 2 ║ Balls ║ 1 ║ ║ 3 ║ Shoes ║ 1 ║ ║ 4 ║…
user2930009
43
votes
4 answers

Oracle SYS_CONNECT_BY_PATH equivalent query into SQL Server

I'm trying to convert a complex query involving the Oracle SYS_CONNECT_BY_PATH syntax into SQL Server: SELECT DISTINCT TO_CHAR(CONCAT(@ROOT, SYS_CONNECT_BY_PATH(CONCAT('C_',X), '.'))) AS X_ALIAS ,…
Platus
  • 1,753
  • 8
  • 25
  • 51
1
2 3
99 100