Multi-level catalog database design (reproduced, please indicate the original author) In practice, often encounter a case where you need multi-level catalog, such as design a forum, its layout is graded, and in order to flexibly expand, this level is not The limit is limited, that is, the designer does not limit the user settings. In this case, I proposed the design plan, first design the following table (as an example of SQL Server)
Create Table [Listing]
[ID] [INT] Identity (1, 1) Not NULL,
[Layout name] [varchar] (30) collate chinese_prc_ci_as not null,
[Layout level] [varchar] (30) collate chinese_prc_ci_as not null,
[Super Layer ID] [INT] NOT NULL
) On [primary]
Among them, the top layout of the top layout is its own layout ID.
Enter the following data:
id
Layout name
Layout level
Super level layout ID
1
learning English
1
1
2
Four-sixth class
2
1
3
GRE
2
1
4
GRE composition
3
3
5
GRE vocabulary
3
3
6
GRE reading
3
3
Seduce
GRE fill in the blanks
3
3
8
GRE composition mutual evaluation
4
4
9
GRE composition outline
4
4
10
TOEFL
2
1
11
TOEFL composition
3
10
12
TOEFL listening
3
10
13
TOEFL reading
3
10
14
TOEFL syntax
3
10
15
physical education
1
15
16
football
2
15
In one
Chinese football
3
16
18
World football
3
16
19
basketball
2
15
20
CBA
3
19
twenty one
NBA
3
19
Create the following stored procedure to read all superi-level layout IDs
- Create a stored procedure of a layout traversing superior directory
Create Procedure TraversebyName
@Name Varchar (30) - Layout Name
AS
Declare @i int - Level cycle variable
Declare @str varchar (30) - temporary string variable
Set @ Str = @ Name - First set the initial value to find the layout name
SELECT @ i = Lombie Level FROM Table - Get layout level
Where layout name = @ Str
Set @ i = @ i-1
Create Table # tmptable - Create a temporary table and store each layout ID
(
IID INT IDENTITY (1, 1), - Temporary Auto Number for sorting
itemname varchar (30)
)
While (@i> 0) - loop read the upper level ID
Begin
SELECT @ str = layout name from layout table
WHERE ID
in (SELECT superior layout ID from version
Where layout name = @ STR)
INSERT INTO #tmptable (itemname) VALUES (@str)
Set @ i = @ i-1
end
- Press the level from high to low list name Select ItemName from #tmptable Order by Iid DESC
Drop Table #tmptable
Execute this stored procedure:
EXEC TRAVERSEBYNAME 'China Football'
The results are as follows:
physical education
football
This way to successfully solve the difficulty of reading all superi-level layouts in this table structure.
