The design of hierarchical relational database table, infinite level

The concept of the data hierarchy:

Data hierarchy is an important relation of expressing data, in the design of database , such as: organizational structure decomposition, work task decomposition, Division of administrative divisions, etc. are the typical examples of hierarchical relational data.

　　The data that expresses the hierarchical relationship generally requires the following properties to be implemented:

1. The maximum number of cascading levels for a hierarchy. such as: Yuhua District, Changsha, Hunan Province, China, is 4 floor.

2. Can reflect the order relationship between the same level. such as: Changsha must be in front of Hengyang (because it is the capital).

3. Can get any level of parent node-level child node (child node Set).

4. Direct access to any level of data. For example: Data at the third tier of the city level.

5. Can facilitate the construction of hierarchical relational expression tree.

6. Changes in hierarchy and sequencing do not affect the internal logical relationships of other data.

　　There are generally three ways to represent hierarchical relationships of data:

　　1. Create multiple database tables:

such as: Country:id (primary key) country_name

Province:id (primary key) country_id (foreign key), Province_name

City:id (primary key), province_id (foreign key), City_name

This method is relatively simple, but the design flexibility is not enough, data processing is more troublesome.

　　2. Using the table's self-correlating foreign key reference

such as: District_info:id (primary key), parent_id, (foreign key) District_info

The hierarchical relationship is established by referencing the primary key (ID) of the foreign Key (parent_id).

Advantages: Infinite level of hierarchical relationship, strong extensibility. For Oracle databases, data queries can be implemented simply by using the Start with,,, Connect by--statement.

Disadvantage: cannot clearly see the hierarchical relationship, cannot realize the sorting.

　　3. Using coding to achieve hierarchy

District:id,code,name where code is characterized by the superior encoding is the prefix of the subordinate code

Pros: By encoding the content can easily go back to the hierarchical relationship of data.

Disadvantage: Using the like method or function query to achieve a subset of the lookup, less efficient, can achieve a hierarchical relationship of limited series (affected by the length of the Code field).

　　Optimization scenarios:

The advantage of set 2,3 is to design the table by the method of fixing the length of the coding order.

District:id,code,parent_id,name

Where ID is the primary key, CODE: specified as 4-bit level. The performance data are as follows:

Id	CODE	parent_id	NAME
1	1		China
2	10001	1	Hunan
3	100010001	2	Hengyang
4	100010002	2	Changsha
5	0001000100020001	4	Yuhua

As can be seen from the data, parent_id can directly express the hierarchy of the relationship between the subordinate

Code code can visually express the relationship between the hierarchical relationship and the order of the same level.

If you want to put Changsha in front of Hengyang, you need to swap their code, and the external reference relationship is implemented by ID, will not be affected by it.

Gets the node's parent level node:

SELECT * from DISTRICT where id= (select parent_id from DISTRICT where id=2)

Gets a list of the node's child-level nodes:

Select * from DISTRICT Where parent_id=2

Get all the child node information:

Select level, * from DISTRICT D Start with parent_id=2 Connect by D.parent_id=prior t.id

Or--sort mode

Select * from DISTRICT D Where d.code like ' 00010001% ' Order by CODE

Gets the node information of a certain level (N) (Implementation sort, meaning of fixed level code length):

Select * from DISTRICT Where LENGTH (CODE) =4*n and code like ' 0001% ' Order by CODE ...

Original: http://www.blogjava.net/jelver/articles/224432.html

Design of hierarchical relational database tables, infinite levels (RPM)