For simplicity and just for illustration, I used SQL Server
2005 Express Edition as the backend database. As a typical and efficient
solution, I have also utilized stored procedures to encapsulate most of the
fundamental business operations. To follow along with the basic idea of the web
tag project in this article, you need to download the source files at the end
of this article.
In this article, we will design a database named Database.mdf, which contains two tables - section for tag categories and note
for tag info, respectively.
Designing the Data Tables
For simplicity, let us use a figure to illustrate the
structures for table note and section,
as shown in Figure 6.
Figure 6 - Structures and relations for table note and section
Since the database diagram in the source code also contains
the explanation for each of the fields of the two tables above, let us continue
to discuss the stored procedures in this database.
Designing the Stored Procedures
In traditional ASP.NET applications, the stored procedure is
the typical solution relating to the backend database to achieve the three-tier
based architecture. To achieve the above listed functions, we have also
leveraged the stored procedure to encapsulate the ground floor data operations.
In this web tag application, there are 9 stored procedures,
which are AddNote, AddSection, DeleteNote, DeleteSection, EditNote,
RenameSection, MoveNote, TransferNote, and MoveSection, respectively. The
following gives their related short explanations:
AddNote: add the tag info to the system
AddSection: add the tag category info to
DeleteNote: delete the tag info from the
DeleteSection: delete the tag category
info from the system
EditNote: edit the existing tag info in
RenameSection: rename the tag category in
MoveNote: move the existing tag between
the tags to change its order in the system
TransferNote: move an existing tag from a
category to another category in the system
MoveSection: change the order between the
Still for brevity, we do not plan to list the related
scripts for the above stored procedures, while you can refer to the source code
Next, we will shift to examineing the system design and the