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
21
votes
3 answers

php / Mysql best tree structure

I have to build a tree that will contain about 300 nodes inside it. The tree has no depth limitations. So it can have 3 or 15 levels. Each node can have an unlimited number of children. The priority is to get a complete tree / subtree the faster as…
Marm
  • 863
  • 2
  • 15
  • 30
21
votes
1 answer

Sorting a subtree in a closure table hierarchical-data structure

I would like to ask you to help me with the problem with sorting of the hierarchical data structure stored as a closure table. I wanted to use this structure to store my website menu. Everything works fine, but the problem is that I do not know how…
JCZ
  • 430
  • 8
  • 18
19
votes
2 answers

Sql query for tree table

I have a table with tree structure: id parentId name ---------------- 1 0 Category1 2 0 Category2 3 1 Category3 4 2 Category4 5 1 Category5 6 2 Category6 7 3 Category7 In SQL query result I…
ihorko
  • 6,855
  • 25
  • 77
  • 116
17
votes
2 answers

efficient function to retrieve a queryset of ancestors of an mptt queryset

Does anybody have an efficient algorithm to retrieve all ancestors of an mptt queryset? The best I could think of so far is something like this: def qs_ancestors(queryset): if isinstance(queryset, EmptyQuerySet): return queryset …
Bacon
  • 2,155
  • 4
  • 23
  • 31
17
votes
8 answers

MySQL Recursive get all child from parent

i have this case using recursive query on Mysql to find lv 2 and lv3 child on one table... database structure i'm using: id name parent 1 A 0 2 B 0 3 C 0 4 D 1 5 E 1 6 F 2 7 G 2 8 H 3 9 I 3 10 …
Bakti Wijaya
  • 447
  • 1
  • 6
  • 21
17
votes
6 answers

How can I break referential integrity briefly, within a transaction, without disabling the foreign key constraint?

I have a table with 3 columns: ID, PARENT_ID, NAME PARENT_ID has a foreign key relationship with ID in the same table. This table is modeling a hierarchy. Sometimes the ID of a record will change. I want to be able to update a record's ID, then…
aw crud
  • 8,791
  • 19
  • 71
  • 115
16
votes
1 answer

MySQL Closure Table hierarchical database - How to pull information out in the correct order

I have a MySQL database holding hierarchical data using the Closure Table method. A simple sample database create script follows the question. My issue at the moment is how do I pull data out of the database in the correct order? I'm currently using…
Justin808
  • 20,859
  • 46
  • 160
  • 265
15
votes
4 answers

(ID/ParentID) list to Hierarchical list

MyClass consists of ID ParentID and List as Children I have list of MyClass like this ID ParentID 1 0 2 7 3 1 4 5 5 1 6 2 7 1 8 6 9 0 10 9 Output (Hierarchical list) as List 1 __ 3 |__ 5__ 4 |__ 7__ 2__ 6__…
Rami Alshareef
  • 7,015
  • 12
  • 47
  • 75
15
votes
2 answers

What tool generates diagrams from SQL Server hierarchical data?

Is there a tool that works with SQL Server to generate tree-like diagrams from a hierachical data model? I am working with a large geographical hierarchy, and would like to visualize it. Here is an example. I have a NodeHierarchy table that stores a…
15
votes
8 answers

get parents and children of tree folder structure in my sql < 8 and no CTEs

I have a folder table that joins to itself on an id, parent_id relationship: CREATE TABLE folders ( id int(10) unsigned NOT NULL AUTO_INCREMENT, title nvarchar(255) NOT NULL, parent_id int(10) unsigned DEFAULT NULL, PRIMARY KEY…
dagda1
  • 26,856
  • 59
  • 237
  • 450
15
votes
1 answer

Is there a practical way to use the hierarchyID datatype in entity framework 4?

As it stands now, the CLR UDTs including HierarchyID aren't supported in Entity Framework 4. HierarchyID.ToString() is useful, but breaks down once any item has 10+ siblings (the basic structure is /3/4/12/ or /3/4/2/ so the 12th node will sort…
15
votes
10 answers

Achieve hierarchy, Parent/Child Relationship in an effective and easy way

I have a table like create table site ( site_Id int(5), parent_Id int(5), site_desc varchar2(100) ); Significance of the fields: site_Id : Id of the sites parent_Id : Parent id of the site site_desc : though not relevant to the question but it has…
Sashi Kant
  • 13,277
  • 9
  • 44
  • 71
14
votes
2 answers

hierarchical data in a database: recursive query vs. closure tables vs. graph database

I'm starting on a new project that has some hierarchical data and I'm looking at all the options for storing that in a database at the moment. I am using PostgreSQL, which does allow recursive querying. I also looked into design patterns for…
14
votes
3 answers

How to self JOIN recursively in SQL?

I have a table: Series ======== ID SeriesName ParentSeriesID A series can be a "root" series, (ParentSeriesID is 0 or null) or it can have a Parent. A series can also be several levels down, i.e. its Parent has a Parent, which has a Parent,…
Neil N
  • 24,862
  • 16
  • 85
  • 145
14
votes
6 answers

Converting flat structure to hierarchical

I need to create function which will be able to convert flat object to recursive object. Here is my example: I have flat array: var flatArray = [ { Description: "G", guid: "c8e63b35", parent: null, }, { …
Adam Mrozek
  • 1,410
  • 4
  • 26
  • 49