Introduction

In the first two articles of this series we mainly dwelled upon the SEO related theories. What really attracts our interests may be the details and tips in building a practical ASP.NET 4.0 based web application. Starting from this article, we'll focus upon the practical things –developing a commonly-used Q&A module that a real ASP.NET website frequently contains. Of course, our main concerns will be around a searching sub-module in it, through which I'll show you many search engine and SEO related topics. However, a full SEO topic, as mentioned previously, will also revolve around website architecture and buffering techniques. So, in later parts of this series I'll also bring them to you. Anyway, before the real story and for you to gain a whole idea with the topic in this series, I first want to introduce to you the infrastructure of the Q&A module.

Introduction

Generally speaking, the main functionalities of a Q&A module would include components, such as the questioner posting questions, responders providing related answers, selecting the best answer, and the background administrator management. And usually, the user score module will also be contained.

In real cases, the Q&A feature requires the participation of several parties and the multi-assistance from them. So, the Q&A module is a highly interactive one. The more participants are, the greater their roles will be and the better the final result. Therefore, the creation of an online Q&A module is often used a means of sticky customers to increase the popularity. In this way, at the time when users lend help to other people, they also accumulate points for their own consumption of some substances in the websites. So, the score sub module is one of the core modules of an online Q&A module. Due to the facts that there are possibly a large number of participants in the Q&A module, and many operations are linked with the points, this kind of module is generally more complicated than a single user interaction module (such as voting, shopping cart, etc.). This article will take you to walk through the core of the Q&A module and frequently-used functions.

Design the Q&A Process

As addressed above, the heart of the Q&A module contains several parts, including asking a question, answering the question, selecting the best answer, and calculating the score, followed by some of the functionalities in the background, such as managing catalogs, questions, and users. Most of these backend functions in the background are dominated by statistics, while general operations are completed by the users themselves in the foreground. And especially, some of these functions can be displayed and hidden automatically through judging a number of relevant parameters, and does not require the administrator or users to manually set the related states.

Of course, generally the system should allow the administrator to mandatorily alter the management state of some data in the background, in order to control the flow direction of the entire answer module. The following sections will focus on the four primary functions of the core module, i.e. asking a question, reply, setting the best answer, and shutting down the question.

Submitting question module

A common prerequisite for all the above four sub modules is the need to log into the system first, since all of them are either the asking sides or the answering sides, so that the system can match the information of the questioners or answers to that in the database, and further make judgment of the operating rights, as well as calculating the related score.

After logging in, the questioner must first enter the new question page (~\Foreground\aspx\NewQuestion.aspx), select the appropriate catalog and fill out the question (or questions) and finally submit the question. When the question is submitted to the server, info in three tables, namely the question table (QAQuestion), user information table (QAUserInfo, in this case used to hold the questioner related info), and global settings table (QAConfig), will be updated. Here, the question table is used to record the current submitted question, updating the user information table refers to updating statistics number of questions (if there is a reward this will also relate to altering the score), while the global settings table related updating is to update the global number of unresolved questions.

Answer module

After the respondent logs in and enters some questioning page, he submits the answer to the corresponding question. Of course, the premises of submitted answers, at this time, include:

• The question is not closed by the questioner (or administrator)
• The question has not yet the best answer
• Respondent himself has not answered this question before

Only the above three conditions are met simultaneously, can the respondents submit answers and further the answers be stored to the database correctly. Related database operation involves two tables, namely, the answer table (QAAnswer) and user information table (QAUserInfo, in this case used to hold the respondent related info). In this case, the answer table is used to log the current user-submitted answer, while the user information table will be used to update the answer count of the respondent. The asking questions process and answering process are shown in Figure 1 and 2, respectively.

Figure 1: Asking question process

Figure 2: Answering question process

After the user submits the answer, questioners can select from all the answers the best one most helpful to them, and set it as the best answer. If, within a certain time, there is no satisfaction answer to his question, he can also turn off the question. Whether the question has already a best answer or been shut down, no user will be allowed to add answers, which can be seen from the above figure.

Set up best answer module

As hinted previously, setting up the best answer is handled by the questioner. He can set his most satisfied answer as the best answer. At this time, the premises of setting the best answer include:

• The question is not closed by the questioner (or administrator).
• The question has not yet the best answer.

Only when both of the above two conditions are met, can setting the best answer becomes valid. Related data operations will involve 5 operations in 4 tables, namely:

• The state in the QAQuestion table changes, changed to "resolved".
• The state in the QAAnswer table changes, changed to "is the best answer".
• Update the questioner related data in the table QAUserInfo.
• The respondent related data in the table QAUserInfo also gets updated.
• The number of "solved questions" in the global settings table increases, while the number of "unsolved questions" reduces.

As you've seen, the best answer related operations cover almost all database tables in the Q&A module. This also exhibits the core position of setting up the best answer in the entire Q&A module. Whether the questioners or the respondents, all of them target to get the "best answer". And also, the "win" of the "best answer" will also provide truly valuable information for the entire Q&A module - all the "best answers" will form a knowledge base with wide knowledge and more accurate answers, for later queries and use.

Close question module

The question close operation is also manipulated by the questioner, when the premise is the same as setting the best answer: the question is not closed, and there is no "best answer" yet.

Closing a question will involve 2 database tables, QAQuestion and QAAconfig, related operations. Status of the corresponding question in the question table will be from "open" to "closed", while the number of "unsolved questions" in the global settings table will reduce. Although the "closed" question is also an unsolved one, the "closed" question does not belong to the statistics scope of the "valuable issues", which therefore will be excluded.

Apart from the "ask" and "answer" functionalities of the core of the foreground module, the background management function of the administrator is relatively important. Because the Q&A module bears strong interactive feature, and correspondingly, users also have high independent rights. At this time, an administrator is required to manage and regulate some of the related stuffs. But, because there are a lot of similarities between these management functionalities and many other modules, this series later will provide more comments. Below simply lists the basic functionalities the backend needs to implement.

(1) Catalog management

In the catalog management part, the administrator can add or modify the problem catalogs. And also, the administrator can select a parent directory, so that an infinite-level directory tree structure can be supported (typically 2 to 3 layers are sufficient). On the whole, this function is used to categorize the problems for statistics and queries.

(2) Question management

This function is used to make statistics about the info, such as status and related links, concerning the submitted questions, to help administrators manage questions.

(3) User management

This function is used to display user information and relevant statistical information about questions whether asked or answered.

Figure 3 and 4 illustrate the related flowchart associated with user selecting the best answer and closing questions.

Figure 3: The questioner sets the best answer flowchart

Figure 4: The questioner shuts down the question flowchart

Starting from the next section, we'll introduce the table definitions in the Q&A system.

Database Design

There are five tables in the Q&A module, i.e. QAConfig, QAUserInfo, QACatalog, QAQuestion, and QAAnswer, all of which are contained in the database QA. For simplicity, we use figures to illustrate the related scheme definitions of these tables.

Table structure definitions

Figure 5: Structure for table QAConfig

The field Solved in table QAConfig represents the number of solved questions, while Unsolved indicates the number of unsolved ones.

Figure 6: Structure for table QAUserInfo

For table QAUserInfo, some of the field meanings are given below.

• Mark: represents the total score the user wins
• RewardMark: the reward mark towards the respondent
• PaidMark: the paid mark published by the questioner
• PunishMark: the punish mark under the special case
• SolvedQuestion: the number of the overall solved questions
• AnswerCount: the total number of answers submitted by the respondent (not necessarily solved)
• AnswerAcceptCount: the total number of answers selected as the best ones
• QuestionSolvedCount: the total number of solved questions (towards the questioners)
• QuestionUnsolvedCount: the total number of unsolved questions (towards the questioners)
• QuestionCancelledCount: the total number of closed questions (towards the questioners)

Note, for the QAUserInfo table, a questioner may be also a respondent, and vice versa.

Figure 7: Structure for table QACatalog

For table QACatalog, some of the field meanings are given below.

• Status: the status of the catalog (1: open; 0: closed)

Figure 8: Structure for table QAQuestion

For table QAQuestion, some of the field meanings are given below.

• CatalogId: the catalog id that the question belongs to
• UserName: the name of the questioner
• Title: the title of the question
• Text: the content of the question
• State: the state of the question (0: unsolved; 1: solved: 2: closed)

Figure 9: Structure for table QAAnswer

For table QAAnswer, some of the field meanings are given below.

• UserName: the name of the answerer
• Text: the content of the answer
• BestAnswer: whether this answer is the best (1: yes; 0: no)
• PostDatetime: the date time submitting the answer
• VoteNice: the total count of the favorable votes
• VoteBad: the total count of the unfavorable votes

Besides the above tables, there is also a stored procedure named sp_SearchQuestion defined in the database QA. This stored procedure is used to retrieve a list of questions that meet the specified condition (maybe an array of keywords). For detailed use, please refer to the method SearchQuestionByKeyWord defined in the file QuestionAndAnswersCache.cs.

Table relationships

For the readers to gain a full idea of the sample application, we also show the dependency relationships of the above tables, as given below:

Figure 10: The relationships of the tables defined in the database QA

Note, in the subsequent section, as soon as we set up the data access layer, the above the dependency relationships diagram is also generated in the .edmx designer. For brevity, I'll no more exhibit it to you.

Using LINQ to Entities to Build up DAL

In this section, we are going to use LINQ to Entities to construct the data access layer of the sample system. As LINQ to SQL, LINQ to Entities is also a member of the LINQ family, used as an ORM framework to compose the persistence tier of database-driven systems. However, it is introduced a bit later in .NET 3.5 SP1 (Visual Studio 2008 SP1).

Although detailing LINQ to Entities is beyond the range of this article, it is still necessary to mention the important differences between LINQ to SQL and LINQ to Entities. LINQ to SQL can only be used together with Microsoft SQL Server, while LINQ to Entities not only can be used with Microsoft SQL Server but also can be used with other famous databases (such as Oracle, DB 2, etc.), which provides more flexibility for ORM persistence.

Create ADO.NET Entity Data Model

Right click the sample project QA and create a folder named DAL. And further, right click the folder DAL to select "Add | New Item..." to open the "Add New Item" dialog. Next, click the Data tab to add an ADO.NET entity data model file named QAModel.edmx, as shown in Figure 11.

Figure 11: Add an ADO.NET entity data model file named QAModel.edmx

After that, the "Entity Data Model" wizard appears. In the first step, select "Generate from database". In the third step, select the proper database objects (the stored procedure sp_SearchQuestion should also be selected), as shown in Figure 12.

Figure 12: Select the proper database objects to generate related object instances

The rest details will be elided. On the whole, the LINQ to Entities ORM tool is easy to use. With nearly all options default, you can achieve the target of mapping the database tables and related stuff to OOP-based objects.

Summary

In this part, we start to construct the Q&A sample application. You've learned the main sub-modules composed of the system, the key flowchart both for the questioners and for respondents. After that, we introduced the five important tables and one stored procedure related definitions. Obviously, to make clear each meaning of the fields will have great effect upon comprehending the whole system. Finally, we used LINQ to Entities ORM tool to construct the DAL of the system. As you've seen, all the stuff in this article only relates to the infrastructure of the sample website, none, till now, SEO related goodies have not even been mentioned. To dig the real pearls out, please go on with the stories...

About Xianzhong Zhu

I'm a college teacher and also a freelance developer and writer from WeiFang China, with more than fourteen years of experience in design, and development of various kinds of products and applications on Windows platform. My expertise is in Visual C++/Basic/C#, SQL Server 2000/2005/2008, PHP+MyS... This author has published 81 articles on DotNetSlackers. View other articles or the complete profile here.