Defining the Database

As mentioned earlier, the main example for this chapter is a help-desk application, which means well need to develop a database. The database will determine what type of information the help-desk application will display. It also determines special component requirements. The database design must consider the following three elements:

• What information you plan to provide to the user

• How you plan to provide the information

• What the formatting requirements are for that data

You can use any database for a help-desk application. For example, if the help-desk application manages a large amount of data, you might want to use a full-fledged database management system (DBMS) such as Microsoft SQL Server 2000. On the other hand, small help-desk applications can rely on something simpler, such as a series of HTML pages accessed through the indexing services provided by IIS. Placing the help pages in a restricted area of the Web site ensures that only authorized personnel can view them and allows you to obtain usage statistics as users make requests .

Note 

This chapter uses a somewhat compressed set of procedures for building the SQL Server database. These instructions will provide you with all the information required to build the database, associated tables, and indexes, but Ill keep theoretical discussions to a minimum. I also wont talk much about security in this example, but you should always include it as part of your database design. If you want a quick method for creating the database, use the script and delimited text file in the Chapter 11\Scripts and Data folder of the source code. All you need to do to use the script is to load it into a utility such as Query Analyzer and run it.

Well begin by creating a database for the help desk application. To add a new database, open SQL Server Enterprise Manager and open the hierarchy for the local server. Right-click the Databases folder and choose New Database. Type the name of the database in the Name field, and then click OK to accept the default settings. Double-click the new entry to open it for use. Figure 11-10 shows the details of the HelpMe database. As you can see, this database uses most of the default settings that SQL Server provides. In this case, I decided to allow unrestricted growth and start with a small database size . The reason is simple: a help-desk application will have strictly controlled entries, and the network administrator should be able to keep a close watch on the size of the file. To better control how the file grows, you might want to use the In Megabytes option in the File Growth group . Using this option allows you to specify specific growth intervals for the database.

Figure 11-10: The HelpMe database will use most of the default settings provided by SQL Server.

Theres just one table for this example. HelpInformation contains the data the application will display on screen. This includes a help title and associated content, as well as a help topic number used to coordinate various help topics. Figure 11-11 shows the structure of this table. Notice that the TopicNumber field is the primary key for this table.

Figure 11-11: A simple table for storing help topics.

Many users will want to search the help desk by topic, so well need to add an index to the database. You can add an index to the example for just this purpose. Right-click on the HelpInformation table entry, and choose Manage Indexes from the All Tasks context menu. Youll see a Manage Indexes dialog box like the one shown in Figure 11-12. Notice that this dialog box already contains an index for the primary key and the custom index well need for the example.

Figure 11-12: Use the Manage Indexes dialog box to add new indexes to the HelpInformation table.

To create the custom index, click New in the Manage Indexes dialog box to display the Create New Index dialog box. All you need to do is select the Title entry and give the index a name such as TitleSearch. Click OK to create the index, and then click Close to close the Manage Indexes dialog box.

At this point, the design of our table is complete. As you can see, were using a very simple database design for this application to ensure there are no interactions or other problems to prevent the application from working the very first time. Remember to set security for the table in the example. All you really need to do is ensure you can access the table from an external application.

The final step is creating data for this table. The number of the help title topic has to be unique to ensure the user can individually access each help topic. Well also want to develop a logical scheme for naming the help topics. The content is the hard part. You need to consider how the client will use the data. For this reason, the text for each Content field entry has to include HTML tags so that the data will appear as the author intended within the target browser. Figure 11-13 shows the sample entries for this example. (See the HelpMe.TXT file for detailed information.)

Figure 11-13: Creating the content for this example means including HTML tags.

Tip 

Pressing Ctrl+M while in the Data In Table ˜HelpInformation window will allow you to add carriage returns to the end of each line of HTML code. This will make the text more readable when viewed as source code in the browser, making the task of debugging the HTML content easier as well.