分类数据表设计的简单再总结

前言：项目中又要用到一个四级分类数据表，之前我曾经在这方面按步就班玩过不少CRUD的操作，感觉工作内容有不少重复，有必要再总结一下，对新手可能会有点帮助，同时以备自己日后再用。

 

1、数据表设计

开门见山，Category表设计如下：

数据表字段简单说明：

列名	数据类型	默认值	备注
Id	int		自增主键
Name	varchar(256)		分类类别名称
ParentId	int	0	父母分类Id
Depth	int	1	深度，从1递增
Status	int	0	状态：0禁用，1启用
Priority	int	0	优先级，越大，同级显示的时候越靠前

说明：在设计实现这个数据表之前，我搜索参考并比较了一下其他无限层级设计方案，比如这一篇和这一篇，虽然本文最终使用了最常见的层级设计而没有采纳另外的几种方法，但是不可否认它们对开阔设计思路是很有启发的。

 

2、简单查询

（1）通常，在实际应用中简单查询某一级别可用（Status等于1）的分类非常简单：

1 2 3 4 5 6 7 8

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

最后按照优先级（Priority）字段逆序即可。

（2）当需要按照某一个Id查找它及它的所有子级或者父级成员，避开递归，直接写sql查询会比较难以下手，而且Sql Server2005之前的版本还需要用到临时表，处理起来不是那么直观。自从Sql Server2005/2008横空出世，利用With语句可用非常轻松地写出查询，下面贴两个开发中经常用到的查询存储过程（Sql Server2005/2008支持）：

a、按照某一个Id查询它及它的所有子级成员存储过程

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

CREATE PROCEDURE [dbo].[sp_GetChildCategories] (@Id int) AS BEGIN WITH Record AS(     SELECT     Id,     Name,     ParentId,     Depth,     Status,     Priority FROM     Category(NOLOCK)     WHERE Id=@Id     UNION ALL         SELECT     a.Id Id,     a.Name Name,     a.ParentId ParentId,     a.Depth Depth,     a.Status Status,     a.Priority Priority FROM     Category(NOLOCK) a JOIN Record b     ON a.ParentId=b.Id )   SELECT     Id,     Name,     ParentId,     Depth,     Status,     Priority FROM     Record     WHERE Status=1     ORDER BY Priority DESC       END

b、按照某一个Id查询它及它的所有父级成员存储过程

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

CREATE PROCEDURE [dbo].[sp_GetParentCategories] (@Id int) AS BEGIN WITH Record AS(     SELECT     Id,     Name,     ParentId,     Depth,     Status,     Priority FROM     Category(NOLOCK)     WHERE Id=@Id     UNION ALL     SELECT     a.Id Id,     a.Name Name,     a.ParentId ParentId,     a.Depth Depth,     a.Status Status,     a.Priority Priority FROM     Category(NOLOCK) a JOIN Record b     ON a.Id=b.ParentId )   SELECT     Id,     Name,     ParentId,     Depth,     Status,     Priority FROM     Record     WHERE Status=1     ORDER BY Priority DESC       END

分析上面两个存储过程，实际上，您也可以提取出下面的两段sql语句直接代替上面的查询存储过程：

c、按照某一个Id查询它及它的所有子级成员sql语句

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

WITH Record AS(     SELECT     Id,     Name,     ParentId,     Depth,     Status,     Priority FROM     Category(NOLOCK)     WHERE Id=@Id  --@Id是外部传入的参数     UNION ALL     SELECT     a.Id Id,     a.Name Name,     a.ParentId ParentId,     a.Depth Depth,     a.Status Status,     a.Priority Priority FROM     Category(NOLOCK) a JOIN Record b     ON a.ParentId=b.Id )   SELECT     Id,     Name,     ParentId,     Depth,     Status,     Priority FROM     Record     WHERE Status=1     ORDER BY Priority DESC

 

d、按照某一个Id查询它及它的所有父级成员sql语句

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

WITH Record AS(     SELECT     Id,     Name,     ParentId,     Depth,     Status,     Priority FROM     Category(NOLOCK)     WHERE Id=@Id  --@Id是外部传入的参数     UNION ALL     SELECT     a.Id Id,     a.Name Name,     a.ParentId ParentId,     a.Depth Depth,     a.Status Status,     a.Priority Priority FROM     Category(NOLOCK) a JOIN Record b     ON a.Id=b.ParentId --匹配关系 )   SELECT     Id,     Name,     ParentId,     Depth,     Status,     Priority FROM     Record     WHERE Status=1     ORDER BY Priority DESC

参数@Id毫无疑问，是你需要在外部程序里传入的参数。选择存储过程或者直接使用sql语句看自己的喜好（个人倾向于写sql语句）。

 

3、项目实践经验之谈

在实际项目中，对于分类表，通常都会做相应的缓存（这种类型的数据通常说多也不多，说少也不少，但是相对比较稳定），总结一下我在web项目中的使用经验（经验之谈，请务必小心甄别取舍）：

（1）、一次性取出数据库中所有可用分类类别数据；

（2）、数据（Category表数据）转换成对应实体Category；

a、Category实体类

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

using System;   /// <summary> /// 分类实体 /// </summary> [Serializable] public class Category : BaseCategory//继承自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; }   }

我们看到，Category实体继承自BaseCategory类，这个类我们定义如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

public abstract class BaseCategory : DotNet.Common.Model.PagerBase //PagerBase 分页基类 {     /// <summary>     /// 一级分类id     /// </summary>     public int FirstCategoryId { get; set; }     /// <summary>     /// 一级分类名     /// </summary>     public string FirstCategoryName { get; set; }       /// <summary>     /// 二级分类id     /// </summary>     public int SecondCategoryId { get; set; }     /// <summary>     /// 二级分类名     /// </summary>     public string SecondCategoryName { get; set; }       /// <summary>     /// 三级分类id     /// </summary>     public int ThirdCategoryId { get; set; }     /// <summary>     /// 三级分类名     /// </summary>     public string ThirdCategoryName { get; set; }       /// <summary>     /// 四级分类id     /// </summary>     public int ForthCategoryId { get; set; }     /// <summary>     /// 四级分类名     /// </summary>     public string ForthCategoryName { get; set; } }

b、接着通过一定的方法或函数，对Category实体类再做一些处理，完善它的层级关系。比如通过递归函数，初始化一次，准备好这些有层级的数据实体：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91

/// <summary> /// 分类实用帮助类 /// </summary> public class CategoryUtil {     /// <summary>     /// 分层级的数据实体字典 key： Id  value：分类实体     /// </summary>     public static IDictionary<int, Category> DictCategories { get; set; }       static CategoryUtil()     {         Init();     }       /// <summary>     /// 根据品类类别构造一个适合查找的dictionary(1～4级品类ID和对应名称)     /// </summary>     private static void Init()     {         //DictProductTypes=//查库，一次取出所有可用分类数据 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>     /// 初始化层级     /// </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);//递归                 break;             }         }     }   }

然后进行第（3）步，进行缓存。

需要特别说明的是，BaseCategory类我们只多设计了8个属性，四个层级（目前为止开发中超过四个层级的我还没有遇到过），当然你可能会问，如果超过4个层级怎么办？曾经看到过有一种通用设计的思路，就是通过一个集合对象（或嵌套的集合对象）进行层级类别的存取，比如泛型Dictionary，LinkedList等等，我还没有尝试实现过，但是设计实现思路确实可以借鉴。

（3）、按照某种策略缓存数据，如每天或者每个月更新一次数据，等等。

（4）、直接查询操作缓存中的分类数据。

 

4、思考

（1）、数据表中Depth字段是不是必要的，是否多余？

（2）、查询时如何避免递归？

（3）、层级过多（比如超过20层级），有没有更好的设计和解决方法？

   … … … …

越想越感到问题多多，期待您的建议和意见。