[Collect] A wide range of infinite-level classification of the database design scheme

The first scenario:

The table is two sheets, a classification table, an information table.
Table 1:
' ID ' int (10),
' CID ' tinyint (3),
' title ' varchar (255),
Table 2:
' CID ' tinyint (3),
' ParentID ' tinyint (3),
' Order ' tinyint (3),
' Name ' varchar (255),

This can be based on the CID = ParentID to determine the upper level of content, the use of recursion to the topmost level.

The second scenario:

Set ParentID to a varchar type, with the parent class ID all in this field, separated by symbols, such as: 1,3,6
This makes it easier to get the ID of each parent classification, and when querying the information under classification, you can use such as: SELECT * from information where CID like "1,3%". However, the operation will be cumbersome when adding classification and transfer classifications.

The third scenario:

Each level of classification increments two digits, so that the number of each level classification is limited to 100, the classification method is mainly coding;
Example:
Class I: 01,02,03
Level Two classification: 0101,0102,0103,0201,0202 ...
Class Three: 010101,010102,010103,010104 .....

Database query using like ' 1% ' will be able to get the first class classification 01 under all sub-categories, very convenient!
If you want to list the tree structure of all the classifications, just use a single statement select * from Pro_class Order by code, and then just a little bit of processing. (where Pro_class is the product classification table, code is the category code).


The database structure is designed as follows:
ID: Category ID, primary key
ClassName: Class name
Classcode: Category encoding
Parent: Father ID
Left_child: Leftmost child ID (or first child)
Right_sibling: Right Brother ID
Layer: Hierarchy (First level Category 1, Level 2nd Category 2, etc.)

[Collect] A wide range of infinite-level classification of the database design scheme