How to Choose the Best Method for Storing Hierarchical Data in a Relational Database?

The best way to store the data in the database of high efficiency relationship database

The method of storing the data in the relationship database

Introduction

The method of selecting the storage level data in the relationship database may be a difficult task. This article discusses various methods and their respective advantages and disadvantages to guide you to make wise decisions.

Weighing the advantages and disadvantages

The key to selecting the right storage method is to get a balance between fast reading time and quick writing time. The adjacent table usually provides faster reading performance, while the nested and bridge tables performed well in terms of writing performance. However, the combination of these technologies is usually the most suitable for specific needs. Storage options and their functions

<.> 1. The adjacent table

column:

ID, Parentid Advantages:

Easy to achieve, the cost of modification of nodes is low

• Disadvantages: The cost of querying ancestors, offspring and paths is high
• <.> 2. Embedding (mptt)
• column: Left value, right value
Advantages:

Low the cost of the ancestors and offspring

Disadvantages: Due to the changeable encoding, the cost of modification of the node is very high

• <.> 3. Bridge connection (closure table with a trigger)
• column: Ancestor, offspring, depth (optional)
Advantages:
• Inquiry about the low cost of the ancestors and offspring, the standardized encoding Disadvantages:
Each node needs to be multiple lines, the cost of inserting, updating and deleting is O (Log N)

<.> 4. Faber (materialization path)

• column: Spectrum
Advantages:
• You can check the offspring cheaply through the prefix query Disadvantages:
The cost of inserting, updating and deleting is O (LOG N), non -relational type
• <.> 5. The nested interval

column: Start, end

Advantages:
• Due to non -volatile coding, the cost of modification of nodes is low, and it has real numbers/floating -point numbers/decimal indicators Disadvantages:
Real/floating point number/decimal representation/accuracy problem
• <.> 6. Plane table
• column:
level, ranking

Advantages: Cheap iteration and pagination

Disadvantages:

High operating costs of mobile and deleting
• <.> 7. Multiple scores
• column:
Each layer of scores a column
• Advantages: Low the cost of the ancestors, offspring, and levels, low cost of insertion, deletion and movement of leaf nodes

Disadvantages:

High cost of insertion, deletion and movement of internal nodes, and the depth depth of the level has a hard limit

A specific precautions of the database

• MySQL/Mariadb: Use CTE to query the adjacent table in the latest version.

  Oracle: Use Connect by to traverse the adjacent table.

  PostgreSQL: Use LTREE data type for materialized paths.

  SQL Server: 2008 provided Hierarchid data type for the formula method and expansion depth representation.

  The best method and additional resources

  This article recommends the use of adjacent tables to maintain the hierarchical structure and use nested sets for query, because it combines the advantages of the two methods. In addition, this article also provides valuable resources for you to further explore:
  The nested interval and the adjacent table
  • The model of layered data
  • In mysql, the hierarchical structure
  • layered data in RDBMS

The above is the detailed content of How to Choose the Best Method for Storing Hierarchical Data in a Relational Database?. For more information, please follow other related articles on the PHP Chinese website!