In this case, we will design a SQL Server database named ST_BLOG_DATA.mdf, which contains seven tables with each
taking their own responsibilities listed below.
—ST_admin for holding users info. For
simplicity we have not included the user registration module. The default
system administrator name and password are admin and 1 respectively. And also, we have not added any blog host
user info—you can add them on your own situation.
—ST_class to hold the blog
categories to facilitate the blog article management.
—ST_link to hold the hyperlinks to
other blogs that the blog host user shows interest in.
—ST_message to hold all the words
left to the current blog host by the valid users.
—ST_news to hold all the blog
articles written by the current blog host.
—ST_replay to hold comments made
to the current blog article by the valid readers.
—codetable to hold authentication
info used to distinguish the human being operations from the robot ones.
Next, to achieve a better understanding with these tables
above, I listed each of their schema definitions in table form below.
Designing the tables
The following tables give the corresponding structure
definitions and field notes of the tables mentioned above, respectively.
Table 1: Structure for table ST_admin
Field name
|
Type
|
Notes
|
ST_admin_user
|
nvarchar(50)
|
Can be null.
The administrator user name.
|
ST_admin_psw
|
nvarchar(50)
|
Can be null.
The password corresponding to ST_admin_user field above.
|
Table 2: Structure for table ST_class
Field name
|
Type
|
Notes
|
ST_c_id
|
bigint
|
IDENTITY (1,1) NOT NULL, Primary Key.
The auto incremental category id.
|
ST_c_name
|
nvarchar(50)
|
NOT NULL.
The blog category name.
|
Table 3: Structure for table ST_link
Field name
|
Type
|
Notes
|
ST_l_id
|
bigint
|
IDENTITY (1,1) NOT NULL, Primary Key.
The auto incremental id for the hyperlink that the blog
host is interested in.
|
ST_l_name
|
nvarchar(100)
|
Can be null.
The shortened hyperlink name.
|
ST_l_url
|
nvarchar(100)
|
Can be null.
The complete url for the above hyperlink.
|
Table 4: Structure for table ST_message
Field name
|
Type
|
Notes
|
ST_id
|
bigint
|
IDENTITY (1,1) NOT NULL, Primary Key.
The auto incremental id for the message that valid
passenger leaves.
|
ST_nickname
|
nvarchar(50)
|
Can be null.
The nickname of the passenger that leaves the word.
|
ST_title
|
nvarchar(50)
|
Can be null.
The title of the word.
|
ST_homepage
|
nvarchar(50)
|
Can be null.
The title of passenger’s homepage.
|
ST_content
|
ntext
|
Can be null.
The details of the words left.
|
ST_mdate
|
datetime
|
Can be null.
The datetime when the word was left.
|
ST_hf
|
ntext
|
Can be null.
Mark when the left word is replied or not in text mode.
|
Table 5: Structure for table ST_news
Field name
|
Type
|
Notes
|
ST_n_id
|
int
|
IDENTITY (1,1) NOT NULL, Primary Key.
The auto incremental id for the blog article.
|
ST_n_author
|
nvarchar(50)
|
Can be null.
The author name of the blog.
|
ST_n_title
|
nvarchar(200)
|
Can be null.
The title of the blog.
|
ST_n_key
|
nvarchar(200)
|
Can be null.
The abstract of the blog.
|
ST_n_content
|
ntext
|
Can be null.
The details of the blog.
|
ST_n_date
|
datetime
|
Can be null.
The datetime for posting the blog.
|
ST_n_hit
|
bigint
|
Can be null.
The times of the blog read by surfers.
|
ST_n_re
|
bigint
|
Can be null.
The times of replies for this blog.
|
ST_c_id
|
bigint
|
Foreign key.
|
ST_n_iscmd
|
bit
|
Can be null.
Mark when the left word is replied or not in text string.
|
Table 6: Structure for table ST_replay
Field name
|
Type
|
Notes
|
ST_r_id
|
bigint
|
IDENTITY (1,1) NOT NULL, Primary Key.
The auto incremental id for the comment.
|
ST_r_nick
|
nvarchar(50)
|
Can be null.
The nick name of the comment.
|
ST_r_title
|
nvarchar(50)
|
Can be null.
The title of the comment.
|
ST_r_content
|
nvarchar(250)
|
Can be null.
The details of the comment.
|
ST_r_date
|
datetime
|
Can be null.
The datetime for making the comment.
|
ST_n_id
|
int
|
Foreign key.
|
Table 7: Structure for table codetable
Field name
|
Type
|
Notes
|
Id
|
int
|
IDENTITY (1,1) NOT NULL, Primary Key.
The field that identifies the id of the code for the
CAPTCHA verification usage.
|
Code
|
nchar(20)
|
Cannot be null. The text string to achieve the CAPTCHA
verification.
|
With the table schemas established, we should further familiarize
ourselves with the relationships between the tables introduced above.
Set up the Table Relationships
Figure 6 illustrates the table reference dependencies
defined inside the database ST_BLOG_DATA.mdf (I created the database diagram
inside Visual Studio Team System 2008; or you can create it using SQL Server
2005/2008).
Figure 6: The reference relationships between the
tables
Again, since this system is a simplified version there are
no complex reference dependencies between the above tables, but there are still
few relations between the three tables as is easily caught sight of from the
figure.
About the Common Modules
Only two points need to be noticed here: the database connection
mode and the general layout of the system.
As for the database connection, I select to directly put the
sample database under the sub-folder App_data of the website for you to more
easily debug the system. However, in real cases, the databases commonly reside
in the SQL Server side which is located at independent machines. So, the database
connection string will be different under these cases. Herein, we only consider
the above simplest situation as indicated in the config file web.config below.
Code Listing 1
<connectionStrings>
<add name="ConnectionString" connectionString=
"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ST_Blog_Data.MDF;
Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
</connectionStrings>
As for the general layout of the system, we select to
leverage the master page introduced in ASP.NET 2.0 since it is the recommended
means to construct real case projects. In our case, there are two types of
master pages: Admin.master (for backend management content pages) and OneColumn.master
(for login.aspx and Message.aspx). Moreover, for greater flexibility we have
also put into use two common ASP.NET Web forms—index.aspx and Show.aspx.
Next, let us set out for the long journey to write the mini
blog system one by one. The first part comes to you is the login system.