Developing an ASP.NET AJAX Server Centric Based Mini Blog System - Part 1
page 6 of 9
by Xianzhong Zhu
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 35221/ 119

Database Design

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.


View Entire Article

User Comments

Title: Khabar   
Name: Sarfraz
Date: 12/20/2010 8:55:44 AM
Comment:
Hello Nice






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2014 ASPAlliance.com  |  Page Processed at 10/25/2014 2:13:18 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search