生活随笔
收集整理的這篇文章主要介紹了
分类数据表设计的简单再总结
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
前言：項目中又要用到一個四級分類數據表，之前我曾經在這方面按步就班玩過不少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)
WHEREStatus=1 ANDDepth=n
|
最后按照優先級（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
|
CREATEPROCEDURE[dbo].[sp_GetChildCategories] (@Id int)
AS
BEGIN
WITHRecord AS(
SELECT
Id,
Name,
ParentId,
Depth,
Status,
Priority
FROM
Category(NOLOCK)
WHEREId=@Id
UNIONALL
SELECT
a.Id Id,
a.NameName,
a.ParentId ParentId,
a.Depth Depth,
a.Status Status,
a.Priority Priority
FROM
Category(NOLOCK) a JOINRecord b
ONa.ParentId=b.Id
)
SELECT
Id,
Name,
ParentId,
Depth,
Status,
Priority
FROM
Record
WHEREStatus=1
ORDERBYPriority 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
|
CREATEPROCEDURE[dbo].[sp_GetParentCategories] (@Id int)
AS
BEGIN
WITHRecord AS(
SELECT
Id,
Name,
ParentId,
Depth,
Status,
Priority
FROM
Category(NOLOCK)
WHEREId=@Id
UNIONALL
SELECT
a.Id Id,
a.NameName,
a.ParentId ParentId,
a.Depth Depth,
a.Status Status,
a.Priority Priority
FROM
Category(NOLOCK) a JOINRecord b
ONa.Id=b.ParentId
)
SELECT
Id,
Name,
ParentId,
Depth,
Status,
Priority
FROM
Record
WHEREStatus=1
ORDERBYPriority 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
|
WITHRecord AS(
SELECT
Id,
Name,
ParentId,
Depth,
Status,
Priority
FROM
Category(NOLOCK)
WHEREId=@Id
UNIONALL
SELECT
a.Id Id,
a.NameName,
a.ParentId ParentId,
a.Depth Depth,
a.Status Status,
a.Priority Priority
FROM
Category(NOLOCK) a JOINRecord b
ONa.ParentId=b.Id
)
SELECT
Id,
Name,
ParentId,
Depth,
Status,
Priority
FROM
Record
WHEREStatus=1
ORDERBYPriority 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
|
WITHRecord AS(
SELECT
Id,
Name,
ParentId,
Depth,
Status,
Priority
FROM
Category(NOLOCK)
WHEREId=@Id
UNIONALL
SELECT
a.Id Id,
a.NameName,
a.ParentId ParentId,
a.Depth Depth,
a.Status Status,
a.Priority Priority
FROM
Category(NOLOCK) a JOINRecord b
ONa.Id=b.ParentId
)
SELECT
Id,
Name,
ParentId,
Depth,
Status,
Priority
FROM
Record
WHEREStatus=1
ORDERBYPriority 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
|
usingSystem;
/// <summary>
/// 分類實體
/// </summary>
[Serializable]
publicclassCategory : BaseCategory
{
publicintId { get; set; }
publicstringName { get; set; }
publicintParentId { get; set; }
publicintDepth { get; set; }
publicintStatus { get; set; }
publicintPriority { 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
|
publicabstractclassBaseCategory : DotNet.Common.Model.PagerBase
{
/// <summary>
/// 一級分類id
/// </summary>
publicintFirstCategoryId { get; set; }
/// <summary>
/// 一級分類名
/// </summary>
publicstringFirstCategoryName { get; set; }
/// <summary>
/// 二級分類id
/// </summary>
publicintSecondCategoryId { get; set; }
/// <summary>
/// 二級分類名
/// </summary>
publicstringSecondCategoryName { get; set; }
/// <summary>
/// 三級分類id
/// </summary>
publicintThirdCategoryId { get; set; }
/// <summary>
/// 三級分類名
/// </summary>
publicstringThirdCategoryName { get; set; }
/// <summary>
/// 四級分類id
/// </summary>
publicintForthCategoryId { get; set; }
/// <summary>
/// 四級分類名
/// </summary>
publicstringForthCategoryName { 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>
publicclassCategoryUtil
{
/// <summary>
/// 分層級的數據實體字典 key： Id value：分類實體
/// </summary>
publicstaticIDictionary<int, Category> DictCategories { get; set; }
staticCategoryUtil()
{
Init();
}
/// <summary>
/// 根據品類類別構造一個適合查找的dictionary(1～4級品類ID和對應名稱)
/// </summary>
privatestaticvoidInit()
{
foreach(KeyValuePair<int, Category> kv inDictCategories)
{
Category model = kv.Value;
switch(model.Depth)
{
default:
break;
case1:
model.FirstCategoryId = model.Id;
model.FirstCategoryName = model.Name;
break;
case2:
model.SecondCategoryId = model.Id;
model.SecondCategoryName = model.Name;
break;
case3:
model.ThirdCategoryId = model.Id;
model.ThirdCategoryName = model.Name;
break;
case4:
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>
privatestaticvoidInitCascadeCategory(Category query, intparentId, intdepth)
{
if(depth < 2)
{
return;
}
foreach(KeyValuePair<int, Category> kv inDictCategories)
{
Category model = kv.Value;
if(parentId == model.Id && model.Depth == depth - 1)
{
switch(depth)
{
default:
break;
case2:
query.FirstCategoryId = model.Id;
query.FirstCategoryName = model.Name;
break;
case3:
query.SecondCategoryId = model.Id;
query.SecondCategoryName = model.Name;
break;
case4:
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層級），有沒有更好的設計和解決方法？
… … … …
越想越感到問題多多，期待您的建議和意見。
總結
以上是生活随笔為你收集整理的分类数据表设计的简单再总结的全部內容，希望文章能夠幫你解決所遇到的問題。
如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。
