Build Your Own ASP.NET 3.5 Website Using C# & VB (53 page)

287

Table 7.7. The Departments table

DepartmentID (Primary Key)

Department

1

Accounting

2

Administration

3

Business Development

4

Customer Support

5

Executive

6

Engineering

7

Facilities

8

IT

9

Marketing

10

Operations

Table 7.8. The Employees table

Emp’ID

Dep’tID

Name

U’name

P’word

City

State

M’Phone

(Primary

Key)

1

5

Zak

zak

zak

San

CA

555-555-5551

Ruvalcaba

Diego

2

9

Jessica

jessica

jessica

San

CA

555-555-5552

Ruvalcaba

Diego

3

6

Ted

ted

ted

San

CA

555-555-5555

Lindsey

Diego

4

6

Shane

shane

shane

San

CA

555-555-5554

Weebe

Diego

5

9

David

david

david

San

CA

555-555-5553

Levinson

Diego

6

1

Geoff Kim

geoff

geoff

San

CA

555-555-5556

Diego

Licensed to [email protected]

288

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

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

CategoryID (Primary Key)

Category

1

Hardware

2

Software

3

Workstation

4

Other/Don't Know

Table 7.10. The HelpDeskStatus table

StatusID (Primary Key)

Status

1

Open

2

Closed

Table 7.11. The HelpDeskSubjects table

SubjectID (Primary Key)

Subject

1

Computer won't start

2

Monitor won't turn on

3

Chair is broken

4

Office won't work

5

Windows won't work

6

Computer crashes

7

Other

Licensed to [email protected]

Database Design and Development

289

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 incorrect. The

ID is really an arbitrary number that we know to be unique; no other information

should be discerned from it.

Relational Database Design Concepts

It is said that data becomes information when we give significance to it. When we

draw tables on paper to decide the logical design of a database, we actually include

significant information about our application (and about the business for which the

application is used). In Figure 7.12, for example, we can see that the employee Zak

Ruvalcaba works in the Executive department.

Figure 7.12. Information about employees

We’ve seen how, in order to optimize data storage and better protect the integrity

of our data, we can extract independent pieces of data, such as department names,

and save them in separate tables, such as the Department table. However, as we did

so, we kept the significance of the original information intact by including references

to the new tables in our existing table. For example, in the Employees table, we have

a DepartmentID column that specifies the department in which each employee

works, as Figure 7.13
illustrates.

This separation of data helps us to eliminate redundant information—for example,

we’d expect to have many employees in each department, but we don’t need to

replicate the department name for each of those employees. Instead, each employee

record refers to the ID of the appropriate department. The benefits of this approach

would be more obvious if more data (such as a department description) were associated with each department; copying all that data for each employee would generate even more redundancy.

Licensed to [email protected]

290

Build Your Own ASP.NET 3.5 Web Site Using C# & VB

Figure 7.13. Related data about employees and departments

These kinds of relationships exist between the HelpDesk, HelpDeskCategories,

HelpDeskStatus, and HelpDeskSubjects tables. Each record in HelpDesk will store

a help desk request. Now, if we stored all the request information in a single table,

its records would look like those shown in Figure 7.14.

Figure 7.14. Information about help desk requests

In order to eliminate redundant data here, we’ve decided to store pieces of this data

in separate tables, and to reference those tables from the HelpDesk table. The only

items of data in the table in Figure 7.14 that aren
’t likely to repeat very frequently are the descriptions and the station numbers. We want users to enter their station

numbers manually, rather than choosing them from a predefined list, so we wouldn’t

gain any benefits from creating a separate table for this item.

Given these requirements, we split the information from Figure 7.14 into four tables:

■ HelpDeskCategories contains the possible help desk request categories.

■ HelpDeskSubject contains the possible request subjects.

■ HelpDeskStatus contains the possible request statuses.

Licensed to [email protected]

Database Design and Development

291

■ The HelpDesk table stores the help desk requests by referencing records from

the other tables, and adding only two original pieces of data itself: the help desk

request description and the station number.

The relationships between these tables are critical, because without them the original

significance of the information would be lost. The relationships are so important

that the database has tools to protect them. Primary keys were used to ensure the

integrity of the records within a table (by guaranteeing their uniqueness); in a moment, we’ll meet foreign keys, which protect the integrity of data spread over multiple tables. In our database’s HelpDesk table, the data depicted in
Figure 7.14
would be stored

physically as shown in Table 7.12
.

Table 7.12. Sample data from the HelpDesk table

RequestID

Emp'ID

StationN'ber

Cat'ID

Subj'ID

Description

StatusID

(Primary

Key)

1

3

5

2

4

Crashes

1

when I open

documents

2

4

7

2

5

Crashes

1

when I

start

Solitaire

Note that, apart from storing data about the request itself, the HelpDesk table also

has an ID column, named RequestID, which acts as the table’s primary key.

Foreign Keys

Technically speaking, a
foreign key
is a constraint that applies to a column that

refers to the primary key of another table. In practice, we’ll use the term “foreign

key” to refer to the column to which the constraint applies.

Unlike primary key columns, a foreign key column can contain NULL, and almost

always contains repeating values. The numeric columns in the HelpDesk table that

reference data from other tables (EmployeeID, CategoryID, SubjectID, and StatusID),

Licensed to [email protected]

292

Build Your Own ASP.NET 3.5 Web Site Using C# & VB