A highly efficient classification algorithm

Source: Internet
Author: User
Tags definition modify connect table definition
Problems to be solved by algorithm classification algorithm
In the website construction, the classification algorithm application is very widespread. When designing an electronic store, it involves the classification of goods, the classification of columns or channels when designing a publishing system, and the classification of software when designing software downloads; It can be said that classification is a very common problem.

I often interview some programmers, and I almost invariably ask them some questions about classification algorithms. Here are a few questions that I often ask. Do you think you can easily answer ^_^?

1, the classification algorithm often manifests as the tree representation and the traversal question. So, excuse me: If you use a table in the database to express the tree category, how many fields should there be?
2. How to quickly restore a tree from this table;
3, how to determine whether a classification is another subcategory of the subclass;
4, how to find a category of all products;
5, how to generate the path of the classification.
6, how to add new classification;

These questions are not easily answered without limiting the number of levels and categories of classification. This article tries to solve these problems.

Data structure of the classification
We know: The data structure of the classification is actually a tree. In the course of data structure, you may have studied the tree algorithm. Since we use a lot of database in the construction of the website, we will talk about the storage of tree in the database.

To simplify the problem, we assume that each node only needs to keep the name of this information. We need to number each node. There are many kinds of numbering methods. An automatic number is often used in a database. This is true in Access, SQL Server, and Oracle. Assume the Number field ID.

In order to indicate that a node ID1 is a parent node of another node ID2, we need to keep a field in the database to show which node this taxonomy belongs to. Name this field Fatherid. such as the ID2 here, its fatherid is ID1.

In this way, we get the data table definition for the classification catalog:

Create Table [Catalog] (

[ID] [int] not NULL,

[Name] [nvarchar] () not NULL,

[Fatherid] [INT] Not NULL

);

Agreement: We agreed to use-1 as the first layer of the classification of the Father code. The category is numbered-1. This is a virtual classification. It has no records in the database.

How to restore a tree
The biggest advantage of the catalog definition above is that it makes it easy to recover a tree-a classification tree. To show the algorithm more clearly, let's consider a simple question: How to display the next level of classification for a category. We know that to query the next level classification of a taxonomy FID, the SQL statement is very simple:

Select Name from Catalog where Fatherid=fid

When displaying these categories, we simply use <LI> to:


<%

REM oconn---Database connection, opened when calling GetChildren

REM FID-----Number of the current category


Function GetChildren (Oconn,fid)

strSQL = "Select id,name from Catalog where fatherid=" &fid

Set rscatalog = Oconn.execute (strSQL)

%>

<UL>

<%

Do as not rscatalog.eof

%>

<li><%=rscatalog ("Name")%>

<%

Loop

%>

</UL>

<%

Rscatalog.close

End Function

%>

Now let's look at how to show all the classifications under the FID. This requires a recursive algorithm. All we need to do is simply call all IDs in the GetChildren function: GetChildren (Oconn,catalog ("ID") is OK.

<%

REM oconn---Database connection, already open

REM FID-----Number of the current category


Function GetChildren (Oconn,fid)

strSQL = "Select Name from Catalog where fatherid=" &fid

Set rscatalog = Oconn.execute (strSQL)

%>

<UL>

<%

Do as not rscatalog.eof

%>

<li><%=rscatalog ("Name")%>

<%=getchildren (Oconn,catalog ("ID"))%>


<%

Loop

%>

</UL>

<%

Rscatalog.close

End Function

%>

The modified GetChildren can complete the task of displaying all subcategories of the FID classification. To show all the categories, just call it:

<%

REM strconn--the string to connect to the database, modify it as appropriate

set oconn = Server.CreateObject ("ADODB. Connection ")

oConn.Open strconn

=getchildren (oconn,-1)

Oconn.close

%>


How to find all products of a category;
Now to address the fourth question we raised earlier. The third question is left as an exercise. We assume that the data table for the product is defined as follows:

Create Table Product (

[ID] [int] not NULL,

[Name] [Nvchar] Not NULL,

[Fatherid] [INT] Not NULL

);

Where the ID is the product number, name is the product, and the Fatherid is the category to which the product belongs.

For the fourth question, it's easy to think of a way to find all the subclasses of this taxonomy, and then query all the products under all subclasses. Implementing this algorithm is actually very complex. The code is roughly as follows:

<%

Function Getallid (Oconn,fid)

Dim strtemp


If Fid=-1 Then

strtemp = ""

Else

strtemp = ","

End If


strSQL = "Select Name from Catalog where fatherid=" &fid

Set rscatalog = Oconn.execute (strSQL)

Do as not rscatalog.eof

Strtemp=strtemp&rscatalog ("id") &getallid (Oconn,catalog ("id")) REM recursive call

Loop

Rscatalog.close


Getallid = strtemp

End Function


REM strconn--the string to connect to the database, modify it as appropriate

set oconn = Server.CreateObject ("ADODB. Connection ")

oConn.Open strconn


FID = Request.QueryString ("FID")


strSQL = "SELECT top * from Product where Fatherid in (" &getallid (Oconn,fid) & ")"

Set Rsproduct=oconn.execute (strSQL)

%>

<ul><%

Do as not rsproduct.eof

%>

<li><%=rsproduct ("Name")%>

<%

Lo



Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.