How to Implement unlimited Classification

Solution 1:

Use recursionAlgorithmIs also the most frequently used, most open-sourceProgramThis is also the case, but generally only four-level classification is used. The database structure design of this algorithm is the simplest. ID of a field in the category table, and FID of a field (parent ID ). In this way, you can determine the upper level of content based on where id = FID, and apply recursion to the top layer.

Analysis, then recursion is performed on the obtained array or object. The load is not too high. However, it is not advisable to classify data into more levels.

In this way, it seems that this category has the advantage of being easy to add, delete, and modify... However, for level-2 classification, this algorithm should be the highest priority.

Solution 2:

Set the FID field type to varchar. Set the parent class ID to this field and separate them with symbols, such as and 6.

In this way, it is easier to obtain the ID of each parent category, and when querying the information under the category,

You can use:

Select * FromCategoryWherePIDLike"1,3%"

Analysis: Compared with recursive algorithms, it has great advantages in reading data. However, if the query efficiency for all parent or subcategories of the classification is not very high, at least two queries are required, in a sense, I personally think it is not in line with the design of the database paradigm. If the number of fields increases to an infinite number, you also need to consider whether the field meets the requirements, and it will be very troublesome to modify and transfer the category.

Currently, the solution similar to the second solution is used in your project. This solution has such a problem in my project. If all the data records reach tens of thousands or even more than 10 million, the classification and orderly classification will come out at a time, and the efficiency is very low. It is very likely that the project processes data.CodeLow efficiency. Improvements are being made.

Solution 3:

Unlimited classification-improved the pre-order traversal tree

What are the characteristics of the ideal tree structure? Small Data Storage redundancy and strong intuition; easy to return the entire tree structure data; can easily return a child tree (convenient for hierarchical loading ); quickly obtain the original spectrum path of a node; insert, delete, and move nodes with high efficiency. With these requirements, I found a lot of information and an ideal tree-structure data storage and operation algorithm, and improved the nested set model ).

The database table design that uses left-right value encoding to store an infinitely hierarchical tree structure is described in detail below.

Below I try to describe this design scheme with a short text, a small number of charts, and related core SQL statements:

First, let's take a tree as an example:

Product
| --- Food
| --- Meat
| -- Pork
| --- Vegetables
| -- Cabbage
| --- Electrical Appliance
| -- TV
| -- Refrigerator

The data record for saving the tree using the left-right value encoding is as follows (set the table name to tree ):
Type_id name LFT rgt
1 product 1 18
2. Food 2 11
3 meat 3 6
4. Pork 4 5
5 vegetables 7 10
6 cabbage 8 9
7 appliances 12 17
8 TV 13 14
9 refrigerator 15 16

The first time I saw the above data records, I believe most people do not know what rules the left value (LFT) and right value (rgt) are calculated based on, this table design does not seem to save the information of the parent node. The left and right values are combined with the tree. See:
1 commodity 18
+ --------------------------------------- +
2 Food 11 12 electrical appliances 17
+ ----------------- ++ ----------------------- +
3 meat 6 7 Vegetables 10 13 TV set 14 15 refrigerator 16
4 pork 5 8 cabbage 9
Please point your finger at the number in it. From 1 to 18, what will be discovered by those who have learned the data structure? Yes, the order in which your fingers move is the order in which the tree is first traversed. Next, let me show you how to use the left and right values of a node to obtain the parent node, the number of child nodes, and the number of layers in the tree.

Assume that we want to traverse the list of node "food" and its children and grandchildren in sequence. We only need to use the following SQL statement:
Select * from tree where LFT between 2 and 11 order by LFT ASC
The query result is as follows:
Type_id name LFT rgt
2. Food 2 11
3 meat 3 6
4. Pork 4 5
5 vegetables 7 10
6 cabbage 8 9

How many child nodes does a node have? Very simple: Total number of children and grandchildren = (right-left-1)/2
take the node "food" as an example, total number of children and grandchildren = (11-2-1) /2 = 4

at the same time, when we display the entire category tree in the list, in order to make it easy for users to intuitively see the hierarchy of the tree, the corresponding indentation is generally performed based on the number of layers of the node, how to calculate the number of layers of nodes in the tree? You only need to query the left and right values. Take the node "food" as an example. The SQL statement is as follows:
select count (*) from tree where LFT <= 2 and rgt> = 11
to facilitate the list, we can create a view for the tree table and add a layer column, you can write a user-defined function to compute the number of layers of the class. The function is as follows: Create Function DBO. countlayer
(
@ type_id int
)
Returns int
as
begin
declare @ result int
set @ result = 0
declare @ LFT int
declare @ rgt int
If exists (select 1 from tree where type_id = @ type_id)
begin
select @ LFT = LFT, @ rgt = rgt from tree where type_id = @ type_id
select @ result = count (*) from tree where LFT <= @ LFT and rgt >=@ rgt
end
return @ result
end
go

Then, we create the following view:
Create view DBO. Treeview
As
Select type_id, name, LFT, rgt, DBO. countlayer (type_id) as layer from DBO. Tree order by LFT
Go

A stored procedure that first traverses a given node and its Child Nodes in sequence:
Create procedure [DBO]. [gettreelistbynode]
(
@ Type_id int -- ID of a given Node
)
As
Declare @ LFT int
Declare @ rgt int
If exists (select 1 from tree where type_id = @ type_id)
Begin
Select @ LFT = LFT, @ rgt = rgt from tree where type_id = @ type_id
Select * From Treeview where LFT between @ LFT and @ rgt order by LFT ASC
End
Go

Now, we use the stored procedure above to list the node "food" and all its children and grandchildren. The query results are as follows:
Type_id name LFT rgt Layer
2 Food 2 11 2
3 meat 3 6 3
4. Pork 4 5 4
5 vegetables 7 10 3
6 cabbage 8 9 4

The design scheme of left-right value encoding is adopted. Because only two queries are required during the class tree's time span, recursion is eliminated, and the query conditions are all numbers, which is highly efficient, the more record entries in the category tree, the higher the execution efficiency. I believe many people are excited about this design scheme. Let's take a look at how to insert, delete, and shift nodes at the same layer in this table structure (change the order of nodes at the same layer).

Suppose we want to add a subnode "beef" under the node "meat", the tree will become:
1 product 18 + 2
+ -------------------------------------------- +
2 Food 11 + 2 12 + 2 Electrical Appliances 17 + 2
+ ----------------- ++ --------------------------- +
3 meat 6 + 2 7 + 2 Vegetables 10 + 2 13 + 2 TV set 14 + 2 15 + 2 refrigerator 16 + 2
+ ------------- +
4 pork 5 6 beef 7 8 + 2 cabbage 9 + 2

after reading the changes in the left and right values of the corresponding node, I believe everyone knows how to write the corresponding SQL script? The following describes the complete procedure for inserting a subnode:
Create procedure [DBO]. [addsubnodebynode]
(
@ type_id int,
@ name varchar (50)
)
as
declare @ rgt int
If exists (select 1 from tree where type_id = @ type_id)
begin
set xact_abort on
begin transaction
select @ rgt = rgt from tree where type_id = @ type_id
Update tree set rgt = rgt + 2 Where rgt >=@ rgt
Update tree set LFT = LFT + 2 where LFT >=@ rgt
insert into tree (name, LFT, rgt) values (@ name, @ rgt, @ rgt + 1)
commit transaction
set xact_abort off
end
go

Then, let's Delete the "TV" node and see what will happen to the tree:
1 goods 20-2
+ ----------------------------------- +
2 Food 13 14 Electric Appliances 19-2
+ ----------------- +
3 Meat 8 9 vegetables 12 17-2 refrigerator 18-2
+ ---------- +
4 pork 5 6 beef 7 10 cabbage 11

the stored procedure is as follows:
Create procedure [DBO]. [delnode]
@ type_id int
as
declare @ LFT int
declare @ rgt int
If exists (select 1 from tree where type_id = @ type_id)
begin
set xact_abort on
begin transaction
select @ LFT = LFT, @ rgt = rgt from tree where type_id = @ type_id
Delete from tree where LFT >=@ LFT and rgt <= @ rgt
Update tree set LFT = LFT -( @ rgt-@ LFT + 1) where LFT> @ LFT
Update tree set rgt = rgt-(@ rgt-@ LFT + 1) where rgt> @ rgt
commit transaction
set xact_abort off
end

Note: deleting a node deletes all child nodes of the node at the same time, the number of deleted nodes is: (the right value of the deleted node-the left value of the deleted node + 1)/2, each node has a unique left value and a unique right value at the same time. Therefore, after a node is deleted, the left and right values of the corresponding nodes must be adjusted: decrease (the right value of the deleted vertex-the left value of the deleted vertex + 1 ).

Finally, let's take a look at what the tree will become after the translation node "electric appliance" moves it and all its children and grandchildren to the node "food:

1 commodity 18
+ ----------------------------------- +
14-12 electrical appliances 17-12 2 + 4 food 13 + 4
+ ---------------------- +
15-12 refrigerator 16-12 3 + 4 Meat 8 + 4 9 + 4 vegetables 12 + 4
+ ------------------- +
4 + 4 pork 5 + 4 6 + 4 beef 7 + 4 10 + 4 cabbage 11 + 4

After the switch, you can observe the changes in the left and right values of the two nodes in the same layer and all their children and grandchildren. We can find an obvious rule, the left and right values of the node "electric appliance" and all its children and grandchildren are reduced by 12, while the left and right values of the node "food" and all its children and grandchildren are increased by 4. The number of node "electric appliance" + its descendant nodes is 2, and the number of node "food" + its descendant nodes is 6. Is there any relationship between them? Do you still remember the comment after the stored procedure of deleting a node? Each node has a unique left value and a unique right value. Let's set the number of nodes to 2, which is equal to the magnitude to be adjusted between the left and right values of the nodes. Based on this rule, we can write a stored procedure similar to the following to implement the same-layer forward node function:
Create procedure [DBO]. [movenodeup]
@ Type_id int
As
Declare @ LFT int
Declare @ rgt int
Declare @ layer int
If exists (select 1 from tree where type_id = @ type_id)
Begin
Set xact_abort on
Begin transaction
Select @ LFT = LFT, @ rgt = rgt, @ layer = layer from Treeview where type_id = @ type_id
If exists (select * From Treeview where rgt = @ lft-1 and layer = @ layer)
Begin
Declare @ brother_lft int
Declare @ brother_rgt int
Select @ brother_lft = LFT, @ brother_rgt = rgt from Treeview where rgt = @ lft-1 and layer = @ Layer
Update tree set LFT = LFT-(@ brother_rgt-@ brother_lft + 1) Where LFT >=@ LFT and rgt <= @ rgt
Update tree set LFT = LFT + (@ rgt-@ LFT + 1) Where LFT >=@ brother_lft and rgt <= @ brother_rgt
Update tree set rgt = rgt-(@ brother_rgt-@ brother_lft + 1) Where rgt> @ brother_rgt and rgt <= @ rgt
Update tree set rgt = rgt + (@ rgt-@ LFT + 1) Where LFT >=@ brother_lft + (@ rgt-@ LFT + 1) and rgt <= @ brother_rgt
End
Commit transaction
Set xact_abort off
End

Note: You can use temporary tables for simultaneous node translation to reduce Code complexity. You do not need to use temporary tables for processing, but the update statement sequence must be carefully considered. Otherwise, if a bug occurs, the damage to the entire category table is astonishing. We strongly recommend that you back up the category table completely before doing the above work.

The stored procedures for the same layer move down are similar to those for the same layer move up. If you are interested, you can write and understand the details. I will not list them here.

finally, I would like to summarize the above scheme of realizing the infinite classification category tree using the Left and Right Value encoding:
advantage: the infinite classification is achieved on the premise of eliminating recursion, in addition, the query conditions are Based on Integer comparison, which is highly efficient. You can perform regular operations such as sequence tables, adding, modifying, deleting, and moving at the same layer to meet your requirements.
disadvantage: because the left-right value encoding method is different from the common Arabic numerals, the order is not intuitively displayed by adding the layers of nodes in the tree, it must be obtained after a simple formula, and it takes some time to thoroughly understand its mathematical model. In addition, this solution is used to write related stored procedures, such as add and delete. At the same layer, the translation node needs to query and modify the entire tree, resulting in high Code complexity and coupling, modification and maintenance are highly risky.