Summary of the design of an infinitus classified data table

A level-4 data table has to be used in the project. I used to perform a lot of crud operations in this aspect. I feel that there are many duplicate work content. It is necessary to summarize it again, it may be helpful for new users and can be used for future use.

 

1,Data Table Design

The category table is designed as follows:

Simple Description of data table fields:

Column name	Data Type	Default Value	Remarks
ID	Int		Auto-incrementing primary key
Name	Varchar (256)		Category name
Parentid	Int	0	Parent category ID
Depth	Int	1	Depth, increasing from 1
Status	Int	0	Status: 0 disabled, 1 Enabled
Priority	Int	0	The higher the priority, the higher the peer level.

Note: before designing and implementing this data table, I searched for and compared other infinite-level design schemes, such as this and this one, although this article uses the most common hierarchical design and does not adopt other methods, it is undeniable that they are very enlightening to broaden the design concept.

 

2,Simple Query

(1) In practice, it is usually very simple to query a certain level of available (status = 1) categories:

SELECT [Id]      ,[Name]      ,[ParentId]      ,[Depth]      ,[Status]      ,[Priority]  FROM [Category](NOLOCK)  WHERE Status=1 AND Depth=n --n>=1

Finally, the priority field is sorted in reverse order.

(2) When you need to search for a specific ID and all its sub-or parent-level members, avoiding recursion, it is difficult to directly write SQL queries, in addition, temporary tables need to be used in versions earlier than SQL server2005, which is not so intuitive to process. Since SQL server2005/2008 was born, it is very easy to use the with statement to write a query. The following are two frequently used query stored procedures in Development (supported by SQL server2005/2008 ):

A. query it and all its sub-member stored procedures based on a specific ID

CREATE PROCEDURE [dbo].[sp_GetChildCategories] (@Id int)ASBEGINWITH Record AS(SELECTId,Name,ParentId,Depth,Status,PriorityFROM    Category(NOLOCK)    WHERE Id=@Id    UNION ALL    SELECTa.Id Id,a.Name Name,a.ParentId ParentId,a.Depth Depth,a.Status Status,a.Priority PriorityFROM    Category(NOLOCK) a JOIN Record b    ON a.ParentId=b.Id)SELECTId,Name,ParentId,Depth,Status,PriorityFROMRecordWHERE Status=1ORDER BY Priority DESCEND

B. query it and all its parent-level member stored procedures based on a specific ID.

CREATE PROCEDURE [dbo].[sp_GetParentCategories] (@Id int)ASBEGINWITH Record AS(SELECTId,Name,ParentId,Depth,Status,PriorityFROM    Category(NOLOCK)    WHERE Id=@Id    UNION ALL    SELECTa.Id Id,a.Name Name,a.ParentId ParentId,a.Depth Depth,a.Status Status,a.Priority PriorityFROM    Category(NOLOCK) a JOIN Record b    ON a.Id=b.ParentId)SELECTId,Name,ParentId,Depth,Status,PriorityFROMRecordWHERE Status=1ORDER BY Priority DESCEND

The preceding two stored procedures are analyzed. In fact, you can extract the following two SQL statements to directly replace the preceding query stored procedures:

C. query it and all its sub-member SQL statements based on a specific ID

With record as (selectid, name, parentid, depth, status, priorityfrom category (nolock) Where id = @ ID -- @ ID is the externally passed parameter Union all selecta. id ID,. name,. parentid,. depth depth,. status status,. priority priorityfrom category (nolock) a join record B on. parentid = B. ID) selectid, name, parentid, depth, status, priorityfromrecordwhere status = 1 order by priority DESC

 

D. query it and all its parent-level member SQL statements based on a specific ID.

With record as (selectid, name, parentid, depth, status, priorityfrom category (nolock) Where id = @ ID -- @ ID is the externally passed parameter Union all selecta. id ID,. name,. parentid,. depth depth,. status status,. priority priorityfrom category (nolock) a join record B on. id = B. parentid -- matching relationship) selectid, name, parentid, depth, status, priorityfromrecordwhere status = 1 order by priority DESC

The parameter @ ID is undoubtedly a parameter you need to input in an external program. Select a stored procedure or directly use SQL statements to view your preferences (I prefer to write SQL statements ).

 

3,Project practice experience

In actual projects, the corresponding cache is usually used for classification tables (this type of data is usually said to be more or less, but relatively stable ), to sum up my experience in web projects, be sure to carefully check and choose between them ):

(1) retrieve all available category data in the database at a time;

(2) convert data (category table data) to corresponding entity category;

A. Category entity class

Using system; // <summary> // category object // </Summary> [serializable] public class category: basecategory // inherits from basecategory {public int ID {Get; set;} public string name {Get; set;} public int parentid {Get; set;} public int depth {Get; set ;}public int status {Get; Set ;}public int priority {Get; Set ;}}

We can see that the category object inherits from the basecategory class, which is defined as follows:

Public abstract class basecategory: DOTNET. common. model. pagerbase // pagerbase paging base class {// <summary> // level-1 category ID /// </Summary> Public int firstcategoryid {Get; set ;} /// <summary> /// first-level category name /// </Summary> Public String firstcategoryname {Get; set ;} /// <summary> /// secondary category ID /// </Summary> Public int secondcategoryid {Get; set ;} /// <summary> /// second-level category name /// </Summary> Public String secondcategoryname {Get; set ;} /// <summary> // level-3 category ID /// </Summary> Public int thirdcategoryid {Get; set ;} /// <summary> // third-level category name /// </Summary> Public String thirdcategoryname {Get; set ;} /// <summary> /// Level 4 category ID /// </Summary> Public int forthcategoryid {Get; set ;} /// <summary> // Level 4 category name /// </Summary> Public String forthcategoryname {Get; Set ;}}

B. Perform some processing on the category object class through some methods or functions to improve its hierarchical relationship. For example, through recursive functions, initialize once and prepare these hierarchical data entities:

/// <Summary> /// practical help class for classification /// </Summary> public class categoryutil {/// <summary> // hierarchical data entity dictionary key: ID value: Category object // </Summary> Public static idictionary <int, Category> dictcategories {Get; set;} static categoryutil () {Init ();} /// <summary> /// construct a suitable Dictionary (1 ~ Level 4 category ID and corresponding name) // </Summary> Private Static void Init () {// dictproducttypes = // check the database, retrieve all available category data at a time to do foreach (keyvaluepair <int, Category> Kv IN dictcategories) {category model = KV. value; Switch (model. depth) {default: break; Case 1: model. firstcategoryid = model. ID; model. firstcategoryname = model. name; break; Case 2: model. secondcategoryid = model. ID; model. secondcategoryname = model. name; break; Case 3: model. thirdcategoryid = model. ID; model. thirdcategoryname = model. name; break; Case 4: model. forthcategoryid = model. ID; model. forthcategoryname = model. name; break;} initcascadecategory (model, model. parentid, model. depth );}} /// <summary> /// initialization level /// </Summary> /// <Param name = "query"> </param> /// <Param name = "parentid"> </param> // <Param name = "depth"> </param> Private Static void initcascadecategory (Category query, int parentid, int depth) {If (depth <2) {return;} foreach (keyvaluepair <int, Category> Kv IN dictcategories) {category model = KV. value; If (parentid = model. ID & model. depth = depth-1) {Switch (depth) {default: break; Case 2: query. firstcategoryid = model. ID; query. firstcategoryname = model. name; break; Case 3: query. secondcategoryid = model. ID; query. secondcategoryname = model. name; break; Case 4: query. thirdcategoryid = model. ID; query. thirdcategoryname = model. name; break;} initcascadecategory (query, model. parentid, -- depth); // recursive break ;}}}}

Then, perform step (3) to cache the data.

It should be noted that the basecategory class has only eight more attributes and four layers (I have not met any of the four layers in development so far ), of course, you may ask, what if there are more than four levels? I have seen that there is a general design idea, that is, access hierarchical classes through a set object (or nested set object), such as generic dictionary and sorted list, I have not tried to implement it, but the design implementation idea can be used for reference.

(3) cache data according to certain policies, such as updating data every day or every month.

(4) directly query the classified data in the Operation cache.

 

4,Thoughts

(1) is the depth field necessary in the data table redundant?

(2) How to Avoid recursion during queries?

(3) Is there a better design and solution for over 20 levels?

... ... ... ...

The more you think about it, the more questions you have. We look forward to your suggestions and comments.

============================== ======================================

Update: According to my comments in this article, I personally think it is a very good solution, and I think of an additional issue closely related to it. For example, for a product table, assume that the basic fields of the product information include the auto-increment primary key ID, product name, Price, production date createdate, and category information corresponding to the product, how should this classification information be designed to quickly query products under a certain category (all products corresponding to this category and its subcategories ), what's more, it helps the backend to modify and maintain data without generating "dirty" data easily? My personal experience is to directly set several fields on the product table, such as firstcategoryid... Forthcategoryid and so on. What do they mean by name ?! To put it simply, it means proper redundancy. queries are very direct and conducive to index creation. I don't know what you think about this problem.

Data Table script download: Category Table

Author: Jeff Wong
Source: http://www.cnblogs.com/jeffwongishandsome/
The copyright of this article is shared by the author and the blog Park. You are welcome to review it. During reprinting, you must provide a link to the original article clearly. Thank you for your cooperation.