SQL Server Alternative non-recursive Unlimited class classification (stored procedure version)

Here are a few of my statistical options:

first Scenario (recursive):

The simple table structure is:
CategoryID Int (4),
CategoryName nvarchar (50),
ParentID Int (4),
Depth Int (4)
In this way, according to the parentid level of the use of recursion to find his superior directory.
and save his parent or subordinate directory for the convenience of adding categoryleft,categoryright.

The second option:
Set a varchar type of categorypath field to save the full path to the directory, separating the parent directory ID from the symbol. For example: 1,5,8,10

The third option:
Method of increasing two digits per level of classification
Example:
First class classification: 01,02,03,04 ...
Class Two: 0101,0102,0103,0104 ...
Class Three: 010101,010102,010103 ...

Analysis, in fact, the third scheme does not really mean to do infinite class classification, and the second scheme, although relatively easy to get each superior and subordinate classification information. However, it will be cumbersome to add and transfer categories.
Furthermore, the database design paradigm is completely violated.

In fact, I have been using the second option. In order to find convenience, I sometimes add CategoryID and Categorypath to the news sheet.

And the algorithm I am going to say today is actually the improved version of the second scheme, and the general classification is to use a table to save the classified information.
And here I am, to create two new tables, one table is to save the category information table, a Save the taxonomy table.

The table structure is as follows:
Table 1:tomi_category
CategoryID Int (4), "No.
CategoryName nvarchar (50), "category name
Depth Int (4), "depth
Table 2:tomi_categorybind
CategoryID Int (4),
Bindcategoryid Int (4),
Depth Int (4),

Add, edit, delete operation a bit troublesome. I am using the stored procedure directly. I don't know if you can read it. Ha ha.
1. Add Category (Category_add)
Copy CodeThe code is as follows:
CREATE proc [dbo]. [Category_add]
@CategoryName nvarchar (50),
@BindCategoryID int,
@CategoryID int Output
As
DECLARE @Success bit
Set @Success =1

--Generate a CategoryID that does not duplicate
DECLARE @i bit
Set @i=0
While @i=0
Begin
Set @CategoryID =left (10000000 + CONVERT (bigint, ABS (CHECKSUM (NEWID ())), 8)
if (Not EXISTS (select CategoryID from Tomi_category where categoryid= @CategoryID))
Set @i=1
End


--Get depth
DECLARE @depth int
Set @depth =0
Select @depth =depth from tomi_category where categoryid= @BindCategoryID
Set @depth = @depth +1

--Insert
BEGIN TRAN
Insert into Tomi_category (categoryid,categoryname,depth) VALUES (@CategoryID, @CategoryName, @Depth)
if (@ @ERROR <>0)
BEGIN
ROLLBACK TRAN
Set @Success =0
End

Insert into Tomi_categorybind (categoryid,bindcategoryid,depth) VALUES (@CategoryID, @CategoryID, @Depth)
if (@ @ERROR <>0)
BEGIN
ROLLBACK TRAN
Set @Success =0
End

Insert into Tomi_categorybind (categoryid,bindcategoryid,depth) Select @CategoryID, bindcategoryid,depth from Tomi_ Categorybind where categoryid= @BindCategoryID
if (@ @ERROR <>0)
BEGIN
ROLLBACK TRAN
Set @Success =0
End
COMMIT TRAN

Print @CategoryID

Each classification has a complete directory structure in Tomi_categorybind. The number of records in a tomi_categorybind is equal to his depth value in Tomi_category.

Image:

2. Edit modification category (Category_edit)
Copy CodeThe code is as follows:
CREATE proc [dbo]. [Category_edit]
@CategoryID int,
@CategoryName nvarchar (50),
@BindCategoryID int
As
--Update
BEGIN TRAN
Update tomi_category set categoryname= @CategoryName where categoryid= @CategoryID
IF @ @ERROR <>0
BEGIN
ROLLBACK TRAN
return 0
End
COMMIT TRAN
--detects if the parent directory has changed
DECLARE @is bit
Set @is =0
If (exists select CategoryID from Tomi_categorybind where categoryid= @CategoryID and bindcategoryid= @BindCategoryID and Depth= (select Depth-1 from tomi_category where categoryid= @CategoryID)))
Set @is =1
Print @is
--Change the depth
if (@is =0)
BEGIN
--Get the depth of the superior catalogue
DECLARE @depth int
Set @depth =0
Select @depth =depth from tomi_category where categoryid= @BindCategoryID
Set @depth = @depth +1
--print @depth
--Changing subdirectories
DECLARE @i int
DECLARE @sCategoryID int
DECLARE @sBindCategoryID int
DECLARE @tCategoryIDList Table
(
CategoryID int,
Flagid tinyint
)
Insert @tCategoryIDList Select c.categoryid,0 from tomi_category C left join Tomi_categorybind B on C.categoryid=b.categor Yid where b.bindcategoryid= @CategoryID order by c.depth
Set @i=1
Set @sBindCategoryID = @BindCategoryID
DECLARE @errs int
Set @errs =0
BEGIN TRAN
while (@i>=1)
BEGIN
Select @sCategoryID =0
Select top 1 @sCategoryID =categoryid from @tCategoryIDList where flagid=0
Set @i=@ @RowCount
--print @sCategoryID
If @sCategoryID >0
BEGIN
--Delete, update
Delete from Tomi_categorybind where categoryid= @sCategoryID
Set @errs = @errs +@ @error
Update tomi_category set depth= @depth where categoryid= @sCategoryID
Set @errs = @errs +@ @error
--Insert
Insert into Tomi_categorybind (categoryid,bindcategoryid,depth) VALUES (@sCategoryID, @sCategoryID, @Depth)
Set @errs = @errs +@ @error
Insert into Tomi_categorybind (categoryid,bindcategoryid,depth) Select @sCategoryID, bindcategoryid,depth from Tomi_ Categorybind where categoryid= @sBindCategoryID
Set @errs = @errs +@ @error
Set @sBindCategoryID = @sCategoryID
Set @Depth = @Depth +1
--print @sCategoryID
--print @sBindCategoryID
--print @Depth
--print "--"
End
Update @tCategoryIDList set flagid=1 where categoryid= @sCategoryID
End
if (@errs >0)
BEGIN
ROLLBACK TRAN
return 0
End
Else
COMMIT TRAN
End


3, delete category (Category_del) will directly delete the sub category
Copy CodeThe code is as follows:
Create proc Category_del
@CategoryID int
As
BEGIN TRAN
Delete from Tomi_category where CategoryID in (select CategoryID from Tomi_categorybind where categoryid= @CategoryID or Bi Ndcategoryid= @CategoryID)
if (@ @ERROR <>0)
BEGIN
ROLLBACK TRAN
return 0
End
Delete from Tomi_categorybind where CategoryID in (select CategoryID from Tomi_categorybind where categoryid= @CategoryID o R bindcategoryid= @CategoryID)
if (@ @ERROR <>0)
BEGIN
ROLLBACK TRAN
return 0
End
COMMIT TRAN

4, Category list, display category (Category_list)

Copy CodeThe code is as follows:
CREATE proc Category_list
As
Select c.* from Tomi_category C-left join Tomi_categorybind B in C.categoryid=b.categoryid where b.depth=1 order by B.bind Categoryid,c.depth

Go

EXEC category_list can directly query the classification level. And show all words, a query can, just judge depth on the line.
Image:

5, the Superior sub-category list (Category_uptree)
Copy CodeThe code is as follows:
Create Proc Category_uptree
@CategoryID int
As
Select c.* from tomi_category C left join Tomi_categorybind B on C.categoryid=b.bindcategoryid where b.categoryid= @Categor Yid ORDER BY C.depth
Go

EXEC Category_uptree 63919523 This allows you to get a complete subdirectory set of categories, conveniently, just a single SQL.
Image:

6, subordinate sub-category list (Category_downtree)
Copy CodeThe code is as follows:
Create Proc Category_downtree
@CategoryID int
As
Select c.* from tomi_category C left join Tomi_categorybind B on C.categoryid=b.categoryid where b.bindcategoryid= @Categor Yid ORDER BY C.depth
Go

EXEC Category_downtree 21779652 This allows you to get a sorted complete subdirectory. For example, to get all the products under a subcategory of a taxonomy and its classification. Convenient, a SQL.
Image:

The above is the first draft, just random test a few times ... There are mistakes, please point out that.

Oh. Reprint please indicate the link, blog Park starter, thank you.
Author: Tomiwong
Time: 2010.07.18