First scenario:
Using recursive algorithms is also the most frequently used, most open source programs do the same, but generally use only four levels of classification. The database structure design of this algorithm is the simplest. Category A field ID in the table, a field FID (parent ID). This allows the upper level to be judged by the WHERE id = FID, using recursion to the topmost level.
Analysis: This database designed by the infinite level, can be said to read the time is very difficult, so most of the program up to 3-4 levels of classification, which is sufficient to meet the requirements, so that all the data read out, and then to get the array or object for recursion. The load itself is still not a big problem. But if you classify it to more levels, that's not a good idea.
So it seems that this classification has a benefit, is to delete and change the time relaxed ... However, for level two classification, this algorithm should be the highest priority.
The second option:
Set the FID field type to varchar, with the parent class ID centered in the field, separated by symbols, such as: 1,3,6
This makes it easier to get the IDs of the superior categories, and when querying the information under the classification,
You can use: SELECT * from category WHERE pid like "1,3%".
Analysis: Compared to the recursive algorithm, the advantage of reading data is very large, but if you find the classification of all the parent classification or sub-classification query efficiency is not very high, at least two times query, from a certain point of view, the individual feel not very consistent with the design of the database paradigm. If you increment to an infinite level, you need to consider whether the field meets the requirements, and it will be cumbersome to modify the classification and transfer classification.
For the time being, a solution similar to the second option is used in your own project. The problem with this program in my project is that if all data records are up to tens or even more than 10W, a one-time classification, orderly grading of the reality out, inefficient. It is highly likely that the project handles data code inefficiencies. is now being improved.
The third option:
An improved pre-order traversal tree with infinite classification----
So what are the characteristics of the ideal tree structure? Data storage redundancy is small, intuitive, easy to return the entire tree structure data, can easily return a subtree (convenient layered loading), fast to get a node of the ancestor Spectrum path, insert, Delete, mobile node efficiency, etc. With these requirements. I looked up a lot of data, and found an ideal tree-structured data storage and operation algorithm, improved forward-order traversal tree model (the Nested Set model).
Principle:
Let's lay out the trees in a horizontal way first. Start with the root node ("Food") and write 1 on his left. Then write 2 on the left of "Fruit" in the Order of the tree (top to bottom). In this way, you walk along the edge of the tree (this is "traversal") and then write numbers on the left and right sides of each node. Finally, we go back to the root node "Food" and write 18 on the right. Below is a tree marked with numbers, and the sequence of traversal is marked with an arrow.
We call these numbers left and right (for example, the left value of "Food" is 1, and the right value is 18). As you can see, these figures are in time for the relationship between each node. Because "Red" has 3 and 62 values, it is followed by a "Food" node with a value of 1-18. Similarly, we can infer that all nodes with a left value greater than 2 and a right value of less than 11 are followed by a 2-11 "Fruit" node. In this way, the structure of the tree is stored through the left and right values. This method of counting the whole tree node is called an improved forward-order traversal tree algorithm.
Table structure Design:
So how can we query all the categories through an SQL statement, and require a few spaces before the subclass to represent subcategories. It's a good thing to query all the categories: SELECT * from category WHERE lft>1 and lft<18 the order by LFT so all the categories are out, but who is the subclass of who is not clear, then how to do? It's not hard to find out if the right value of the right value of the next two records is larger than the second one, so that's his parent class, for example, food's right value is 18 and fruit's right value is 11 then food is fruit's parent class, but it also takes into account the multi-level directory. So with this design, we use an array to store the right value of the previous record, then compare it to the right value of this record, if the former is smaller than the latter, it is not a parent-child relationship, the array is ejected with array_pop, otherwise it is preserved, and then the space is printed according to the size of the array. This solves the problem. The code is as follows
Table structure:
Copy Code code as follows:
--
--The structure of the table ' category '
--
CREATE TABLE IF not EXISTS ' category ' (
' id ' int (one) not NULL auto_increment,
' Type ' int (one) not NULL COMMENT ' 1 for Article Type 2 for product Type 3 for download type ',
' title ' varchar Not NULL,
' LfT ' int (one) is not NULL,
' RGT ' int (one) is not NULL,
' Lorder ' int (one) not NULL COMMENT ' sort ',
' Create_time ' int (one) is not NULL,
PRIMARY KEY (' id ')
) Engine=innodb DEFAULT Charset=utf8 auto_increment=10;
--
--Export the data in the table ' category '
--
INSERT into ' category ' (' id ', ' type ', ' title ', ' LfT ', ' rgt ', ' lorder ', ' create_time ') VALUES
(1, 1, ' Top columns ', 1, 18, 1, 1261964806),
(2, 1, ' Company Profile ', 14, 17, 50, 1264586212),
(3, 1, ' News ', 12, 13, 50, 1264586226),
(4, 2, ' Company Products ', 10, 11, 50, 1264586249),
(5, 1, ' honor credentials ', 8, 9, 50, 1264586270),
(6, 3, ' data downloads ', 6, 7, 50, 1264586295),
(7, 1, ' talent recruitment ', 4, 5, 50, 1264586314),
(8, 1, ' Message board ', 2, 3, 50, 1264586884),
(9, 1, ' president ', 15, 16, 50, 1267771951);
/**
* Show the tree and show all the nodes.
* 1, first get the root node of the left and right values (the default root of the title is the "top-level directory").
* 2, query the left and right values in the root node of the value of the range of records, and sorted according to the left value.
* 3, if the right value of this record is greater than the previous record of the right value is a subcategory, the output time with spaces.
* @return Array
**/
function Display_tree () {
Get the value to the left and right of root
$arr _LR = $this->category->where ("title = ' top Column '")->find ();
Print_r ($arr _LR);
if ($arr _lr) {
$right = Array ();
$arr _tree = $this->category->query ("Select ID, type, title, RGT from category WHERE LfT >=". $arr _lr[' LfT ']. " and LfT <= ". $arr _lr[' RGT ']." ORDER by LfT ");
foreach ($arr _tree as $v) {
if (count ($right)) {
while ($right [count ($right)-1] < $v [' RGT ']) {
Array_pop ($right);
}
}
$title = $v [' title '];
if (count ($right)) {
$title = ' | | '. $title;
}
$arr _list[] = array (' ID ' => $v [' id '], ' type ' => $type, ' title ' => str_repeat (', Count ($right)). $title, ' name ' => $v [' title ']];
$right [] = $v [' RGT '];
}
return $arr _list;
}
}
All right, so long as this all sorts of categories can be queried at once, instead of recursion.
The following question is how to insert, delete, and modify actions
Insert: Insert operation is very simple to find its parent node, and then the left and right values are greater than the left values of the nodes of the node plus 2, and then insert this node, the left values of the parent node plus one and plus two, you can use a stored procedure to operate:
Copy Code code as follows:
CREATE PROCEDURE ' category_insert_by_parent ' (in PID int,in title VARCHAR (m), in type int, in L_order int, in Pubtime int)
BEGIN
DECLARE Myleft INT;
SELECT lft into Myleft from category WHERE id= pid;
UPDATE qy_category SET RGT = rgt + 2 WHERE rgt > myleft;
UPDATE qy_category SET lft = lft + 2 WHERE lft > myleft;
INSERT into Qy_category (type, title, LfT, RGT, Lorder, Create_time) VALUES (type, title, Myleft + 1, Myleft + 2, L_order, p Ubtime);
Commit
End
Delete action:
The principle of deletion: 1. Get the left and right values of the nodes and get their difference plus one, @mywidth = @rgt-@lft + 1;
2. Delete the left and right values between nodes in this node
3. Modify all nodes whose condition is greater than the right value of this node, and manipulate them to subtract @mywidth
The stored procedure is as follows:
Copy Code code as follows:
CREATE PROCEDURE ' Category_delete_by_key ' (in ID INT)
BEGIN
SELECT @myLeft: = LfT, @myRight: = RGT, @myWidth: = rgt-lft + 1
From category
WHERE id = ID;
DELETE from category WHERE LfT BETWEEN @myLeft and @myRight;
UPDATE nested_category SET RGT = rgt-@myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft-@myWidth WHERE lft > @myRight;
Modify:
Fatal modification operation, I saw a long time also did not see what the law out, as long as this unwise, first delete and then insert, as long as the call above 2 stored procedures on it!
Summary: Convenient query, but the modification of the operation is a bit cumbersome, but the general classification of such operations is not a lot, or query used more, and then get a stored procedure is also convenient!
The above third kind of scheme concrete explanation class capacity is from http://home.phpchina.com/space.php?uid=45095&do=blog&id=184675 copy come over, convenient later own view. For the time being, the third option should be considered in all aspects and theoretically. However, no test has been done, in the end how efficient.
Expect a better solution!
