Build Your Own ASP.NET 2.0 Web Site Using C# & VB, Second Edition

Table 7.6. The HelpDeskStatus table

Primary KeyAllow NullIdentitySQL Data TypeColumn Name

YesNoYesintStatusID

NoNoNonvarchar(50)Status

Using SQL Scripts

Yep, there’s a lot of data to type in! While we recommend that you create

the tables yourself by defining the fields outlined here, you can achieve the

same goal using an SQL script that’s included in this book’s code archive.

This script contains SQL code that SQL Server understands, and contains

instructions that create data structures (you’ll learn about SQL in Chapter 8).

If you want to use the downloadable script, we recommend you have a look

over the following tables to get an idea of the structures we’ll be creating,

then read the section called “Executing SQL Scripts” that follows.

We already have a clear idea of the data we’ll store in the Employees and Depart-

ments tables. The other tables will be used to store help desk requests; we’ll discuss

these in more detail in the following pages.

Executing SQL Scripts

If you prefer not to create the data tables manually, you can use the Creat-

eTables.sql script included in the book’s code archive to create the tables for

you. This script is most easily used with SQL Server Management Studio. After

you log in, click the New Query button on the toolbar (or select File > New >

Query with Current Connection). Paste the contents of the CreateTables.sql

script into the window that opens, and press F5 to execute the commands. Note

that if you have already created the Employees table, you should remove the

CREATE TABLE command that creates this table before you hit F5.

The SQL scripts included in the code archive contains all the commands required

for this entire chapter—it even creates the sample data and table references that

are covered later.

Populating the Data Tables

If tables represent drawers in a filing cabinet, rows represent individual paper

records in those drawers. Suppose that our intranet web application was a real

application. As people begin to register and interact with the application, rows

272

Chapter 7: Database Design and Development

are created within the various tables, and are filled up with the information about

those people.

Once the data structures are in place, adding rows of data is as easy as typing

information into the cells in the Datasheet View of a table, which looks a bit

like a spreadsheet. To access it, right-click on the table and select Show Table

Data in Visual Web Developer, or Open Table in SQL Server Management Studio.

You can use the window that opens to start adding data. Let’s add some sample

data to the tables you’ve just created, so that we can test the Dorknozzle database

as we develop the application. Table 7.7 to Table 7.11 represent the tables and

data you should add.

Inserting Data and Identity Columns

If you correctly set the ID column as an identity column, you won’t be al-

lowed to specify the values manually—the ID values will be generated for

you automatically. You need to be careful, because an ID value will never

be generated twice on the same table. So even if you delete all the rows in a

table, the database will not generate an ID with the value of 1; instead, it

will continue creating new values from the last value that was generated for

you.

Keep in mind that a new row is saved to the database at the moment that you

move on to the next row. It’s very important that you remember this when you

reach the last row, as you’ll need to move to an empty row even if you aren’t

adding any more records.

273

Populating the Data Tables

Table 7.7. The Departments table

DepartmentEmployeeID (Primary Key)

Accounting1

Administration2

Business Development3

Customer Support4

Executive5

Engineering6

Facilities7

IT8

Marketing8

Operations10

Table 7.8. The Employees table

M’PhoneStateCityP’wordU’nameNameDep’tIDEmp’ID

(Primary

Key)

555-555-5551CASan

Diego

zakzakZak

Ruvalcaba

51

555-555-5552CASan

Diego

jessicajessicaJessica

Ruvalcaba

92

555-555-5555CASan

Diego

tedtedTed

Lindsey

63

555-555-5554CASan

Diego

shaneshaneShane

Weebe

64

555-555-5553CASan

Diego

daviddavidDavid

Levinson

95

555-555-5556CASan

Diego

geoffgeoffGeoff Kim16

274

Chapter 7: Database Design and Development

The Employees table contains a few more columns than those outlined here, but,

due to the size constraints of this page, I’ve left them out. Feel free to add your

own data to the rest of the cells, or you could leave the remaining cells empty,

as they’re marked to accept NULL.

Table 7.9. The HelpDeskCategories table

CategoryCategoryID (Primary Key)

Hardware1

Software2

Workstation3

Other/Don't Know4

Table 7.10. The HelpDeskStatus table

StatusStatusID (Primary Key)

Open1

Closed2

Table 7.11. The HelpDeskSubjects table

SubjectSubjectID (Primary Key)

Computer won't start1

Monitor won't turn on2

Chair is broken3

Office won't work4

Windows won't work5

Computer crashes6

Other7

What IDENTITY Columns are not For

In our examples, as in many real-world scenarios, the ID values are sequences

that start with 1 and increment by 1. This makes many beginners assume

that they can use the ID column as a record-counter of sorts, but this is a

mistake. The ID is really an arbitrary number that we know to be unique;

no other information should be discerned from it.

275

Populating the Data Tables