TreeOne

A powerfully all-purpose hierarchical tree data structure design model as a best practice in development.
(Demo default by MySQL)

Table Data Structure


CREATE TABLE `tree` (
  `id` int(11) NOT NULL,
  `name` varchar(30) NOT NULL, 
  `level` mediumint(4) NOT NULL DEFAULT '1', // Level number in its barnch
  `branch` mediumint(4) NOT NULL DEFAULT '1', // Means ancestry flag
  `path` varchar(50) NOT NULL DEFAULT '1' // Route to forefather
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Features

Very nifty structure
Strong flexibility
High Performance
Convenient for query
Almost cover all development circumstances

Sample Rows

id	name	level	branch	path
1	——cate-1	1	1	1
3	————cate-1-1	2	1	1.1
4	——————cate-1-1-1	3	1	1.1.1
5	————cate-1-2	2	1	1.2
6	——————cate-1-2-1	3	1	1.2.1
2	——cate-2	1	2	2
7	————cate-2-1	2	2	2.1
9	——————cate-2-1-1	3	2	2.1.1
10	——————cate-2-1-2	3	2	2.1.2
8	————cate-2-2	2	2	2.2

Query Examples

Render whole tree in one query

SELECT * FROM `tree` ORDER BY `branch`, `path`;

Get max deepth of tree
```
SELECT MAX(`level`) FROM `tree`;
```

Get branch root node

SELECT * FROM `tree` WHERE `branch` = 1 ORDER BY `path` ASC LIMIT 1;

Get one’s parent

SELECT * FROM `tree` WHERE `branch` = 1 AND `path` = '1.1';

Get one’s all children(same as one’s all slibings)

SELECT * FROM `tree` WHERE `level` = 3 AND `branch` = 1 AND `path` > '1.1' AND `path` < '1.2';

Get one’s all descendant

SELECT * FROM `tree` WHERE `branch` = 1 AND `path` > '1.1' AND `path` < '1.2' ORDER BY `path` ASC;

Get one’s all ascendant (forms of “Bread-Crumbs”)

SELECT * FROM `tree` WHERE `level` < 3 AND `path` IN ('1.2', '1') ORDER BY `path` ASC

Get one’s immediately preceding sibling

SELECT * FROM `tree` WHERE `level` = 2 AND `branch` = 2 AND `path` < '2.2' ORDER BY `path` DESC LIMIT 1;

Get one’s immediately succeeding sibling

SELECT * FROM `tree` WHERE `level` = 2 AND `branch` = 2 AND `path` > '2.2' ORDER BY `path` ASC LIMIT 1;