Resolution | tree structure tree structure in our application is still very common, such as file directory, BBS, permission settings, departmental settings. These numbers
information is based on a hierarchical structure, and in our current relational database it is difficult to clear the expression. Then you will encounter the tree
in the program
How to deal with the problem of
structure?
recently the author through an ASP Rights Management program easy to solve a problem, now sorted out the readers.
first, the hierarchical data model should be transformed into a relational data model. That means how to access,sql SERVER
in our
This data structure is designed in relational databases such as Oracle and
.
take an example, for example, the following data:
Document Management 1
|----New Document 2
|----Document Modification 3
|----Document Archive 4
| |----View archived INFORMATION 5
| |----Delete archived information 6
| | |----DELETE history document 7
| | |----DELETE official documents 8
|----System Management 9
|----User Management 10
Personnel Management 11
Administration 12
Financial Management 13
This is a very typical hierarchical structure of data, then think about it, how to express it through the form of two-dimensional table? First
looks hard, doesn't it? But there is still a road to be drilled for careful scrutiny.
can do this, all the above permissions as a permission field, then this permission field must have an ID value. I
And then the relational data table and then forcibly add a field-the Membership ID field, which indicates that this permission is the level of permissions
, that is, the ID value of which ID is subordinate to the value. For example: "View archive information" Permission ID value is "5", it is subordinate to "Wen
file permissions, the value of its subordinate ID field should be "4". OK, if that's understandable, then I
's relationship transformation work is basically done.
below we start designing this relational datasheet (for example, SQL Server 7.0):
+-----------+-----------+-----------+-----------+----------+
| Field name | Field Meaning | Field type | Field Size | Field Properties |
+-----------+-----------+-----------+-----------+----------+
| Selfid | Permission ID | Int | 4 | PK |
| Powername | Permission name | Varchar | 50 | Not Null |
| Powerinfo | Permission Information | Varchar | 500 | |
| Belongid | Membership ID | Int | 4 | |
+-----------+-----------+-----------+-----------+----------+
Well, the structure is well designed so you can easily enter your test data.
then, we will focus on how to imitate the hierarchy in the Web page to display this function of the ASP program, this is also the most critical step.
Program list: powerlist.asp
<%
' Database connection
set Conn=server.createobject ("ADODB. Connection ")
Conn.Open "Driver={sql Server};server=chaiwei;database=chaiwei; Uid=sa; Pwd= "
' Open all parent-layer data
set Rs=server.createobject ("ADODB. Recordset ")
Rs. Open "SELECT * from powers where belongid are null order by Powerid", conn,1,3
' Hierarchical number of position variable assignment initial value
Format_i=1
' list Main program segment
do, not rs.eof
' Print parent-layer data information
Response.Write "<a href= ' powerlist.asp?" Selfid= "& RS (" Powerid ") &" &belongid= "& RS (" Belongid ") &" ' > "& RS (" Powername ") &" </a > "
Response.Write "<br>"
' subroutine call, sub-layer data processing
Call Listsubpower (RS ("Powerid"))
Rs.movenext
Loop
' Closes the parent-level dataset
Rs.close
Set Rs=nothing
' sub-layer data processing subroutine
Sub listsubpower (ID)
' Open all child layer data information that is subordinate to the upper Powerid
set Rs_sub=server.createobject ("ADODB. Recordset ")
rs_sub. Open ' select * from Powers where belongid= ' & ID & ' ORDER by Powerid ', conn,1,3
'
layer data
do, not rs_sub.eof
' Hierarchical number of variable progressive cumulative
format_i=format_i+1
' Loop indent format control because the top and two layers do not need to be indented, so the program segment starts at the third level
for I=format_i to 3 step-1
Response.Write "|"
Response.Write ""
Next
' Print child layer data information
Response.Write "|----"
Response.Write "<a href= ' powerlist.asp?" Selfid= "& Rs_sub (" Powerid ") &" &belongid= "& Rs_sub (" Belongid ") &" ' > "& Rs_sub (" Powername ") & "</a>"
Response.Write "<br>"
' Recursive call subroutine itself, the sub-layer data is gradually processed
Listsubpower (Rs_sub ("Powerid"))
Rs_sub.movenext
Loop
' Hierarchical number of statements variable recursive
format_i=format_i-1
' Closes the child-layer dataset
Rs_sub.close
Set rs_sub=nothing
End Sub
%>
In the
powerlist.asp program, we first open the top-level data, display it in the loop, and then design a subroutine listsubpower, called by recursive algorithm in the loop, to open the child layer data information, and in the subroutine internal loop repeatedly call itself, in order to expand the depth of the data 。
In addition, a static variable format_i is used in the program to control the indentation display format.
in this paper, the tree-type structure in the data design, program control to do a simple attempt, the purpose is to make a point, hope that readers through this article to get more enlightenment.
