Design of data table for tree-shaped structure data

There are many places in the application of tree structure, such as custom article classification, allowing the user to add sub-categories indefinitely, such as user communication when the reply, you can reply to reply. Tree structure can completely avoid the user's logical thinking and use of the program restrictions, the maximum freedom to users, to achieve the maximum use of data.

For a relational database that stores data in rows and columns, the tree data cannot be saved directly, so it must be implemented in other ways.

The most basic way is to save a parentid, show each record to its subordinate content, in a recursive form to display the tree. This is the simplest logic, and the implementation is not complex, the code is written to be easy to read and understand, and there is no limit to the number of entries. But if each record is to execute a SELECT, it will undoubtedly put a huge strain on the database. Even once read into the database, in memory over and over again, it is very wasteful CPU resources.

Another way is to add a sort field to each object, so that the data in the time taken out directly in accordance with the tree structure of the order to be taken out, at the time of the display does not need to traverse, as long as the output can be, as long as the sorting field and depth to calculate its indentation depth can be.

The usual sort field uses a simple string, such as the value of the first record is 01, the second item is 02, the subordinate content of the first is 0101,0102, and so on. When taken out of the database, the database sorts the string fields by the size of the values represented by the bytes, so the order after sorting is 01/0101/0102/02/0201 this way. When displayed, this value can represent its ancestor and its depth, which is quite convenient. But one of the main disadvantages of this approach is that the number of content is limited, and if each level is represented by a two-digit number, then the same level will only allow 99 items. With three bits is 999, once exceeded, the entire logic is destroyed. So before using it, you need to consider the maximum number of bars you can tolerate. (This field is not limited to numbers, it can also use letters, such as 00A after 009, until Z, then the three-bit limit is 36*36*36, which is 46,656.) If you use four bits, you reach 1.68 million, which is usually enough. There is a limit to the entries for each layer, but the total number of layers is unlimited, because you can use nvarchar (max) to save the field with no limit to the length of the field. So theoretically you give it 10 bits per layer, so that the entry limit for each layer becomes 36 of 10 times, already enough.