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

❑

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

StatusIDDescriptionSubj'IDCat'IDStationN'berEmp'IDRequestID

(Primary

Key)

1Crashes when I

open documents

42531

1Crashes when I

start Solitaire

52342

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), and the DepartmentID column in the Employees table, are perfect

candidates for the application of a foreign key constraint. Take a look at the ex-

amples shown in Table 7.13 and Table 7.14.

278

Chapter 7: Database Design and Development

Table 7.13. The Departments table’s primary key

DepartmentDepartmentID (Primary Key)

Accounting1

Engineering2

Executive3

Marketing4

Table 7.14. The Employees table referencing records from the

Departments 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

The DepartmentID column in the Employees table references the DepartmentID

primary key in the Departments table. Notice that the DepartmentID primary

key in the Departments table is unique, but the DepartmentID foreign key

within the Employees table may repeat.

As they stand, these tables already have an established relationship, and all the

data in the DepartmentID column of the Employees table correctly matches ex-

279

Foreign Keys