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

Database Design and Development

297

Figure 7.19. Visualizing data tables using a diagram

Figure 7.21. The standard table view

Implementing Relationships in the Dorknozzle

Database

Every table in the Dorknozzle database has a relationship with another table. To

create a foreign key using the diagram, click the gray square to the left-hand side of

the column for which you want to create the foreign key, and drag it over the table

to which you want it to relate.

Let’s give it a try. Start by dragging the DepartmentID column of the Employees

table over the DepartmentID column of the Departments table, as illustrated in

Figure 7.22.

Licensed to [email protected]

298

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

Figure 7.22. Creating a link between Employees and Departments

The designer will open a dialog that shows the details of the new foreign key, like

the one shown in
Figure 7.23.

Figure 7.23. Adding a foreign key

Ensure that your data matches that shown in Figure 7.23, and click
OK
. A new dialog like the one shown in
Figure 7.24 will appear
, allowing you to tweak numerous options that relate to the new foreign key. Leave the default options as they are for

now (though we’ll discuss them shortly), and click
OK
to finish up.

Licensed to [email protected]

Database Design and Development

299

Figure 7.24. Editing the foreign key options

After creating the foreign key, make a quick test to ensure that the relationship is

indeed enforced. Try adding an employee, but set the person’s DepartmentID to

123. You should see an error like the one pictured in
Figure 7.25
. Figure 7.25. The foreign key disallowing the addition of invalid data

If you tried to delete a department with which employees were associated, you’d

generate a similar error.

Table 7.15 shows the foreign keys that we need to establish in the
Dorknozzle database. In our project, the foreign key column has the same name as its corresponding primary key column. Go ahead and create all the foreign keys outlined in

Table 7.15.

Licensed to [email protected]

300

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

Table 7.15. The relationships in the Dorknozzle database

Primary Key

Foreign Key

DepartmentID in the table Departments

DepartmentID in the table Employees

EmployeeID in the table Employees

EmployeeID in the table HelpDesk

CategoryID in the table

CategoryID in the table HelpDesk

HelpDeskCategories

SubjectID in the table HelpDeskSubjects

SubjectID in the table HelpDesk

StatusID in the table HelpDeskStatus

StatusID in the table HelpDesk

Figure 7.26. Creating and visualizing table relationships

When it’
s complete, your relationship diagram should resemble Figure 7.26. After

you add the relationships, save your changes by selecting
File
>
Save Dorknozzle
. Licensed to [email protected]

Database Design and Development

301

When you’re asked to confirm the changes to the database tables you’re altering,

click
Yes
.

Now that you’ve created these foreign keys, you can be sure that all the data stored

in your tables will obey the enforced table relationships. The DepartmentID column

in the Employees table will always reference valid departments, and the HelpDesk

records will always reference valid employees, help desk categories, help desk

subjects, and help desk status codes.

In Chapter 8, you
’ll start learning how to use your new database. Before then, let’s take a moment to analyze the diagram, and learn more about the information it

shows us.

Diagrams and Table Relationships

Relationships describe how data in one table is linked to data in other tables. In

fact, it’s because relationships are so crucial that these types of databases are given

the name “relational databases.” Relationships exist for the sole purpose of associating one table with one or more other tables using primary keys and foreign keys. There are three types of relationships that can occur between the tables in your

database:

■ one-to-one relationships

■ one-to-many relationships

■ many-to-many relationships

One-to-one Relationships

A one-to-one relationship means that for each record in one table, only one other

related record can exist in another table.

One-to-one relationships are rarely used, since it’s usually more efficient just to

combine the two records and store them together as columns in a single table. For

example, every employee in our database will have a phone number stored in the

HomePhone column of the Employees table. In theory, we could store the phone

numbers in a separate table and link to them via a foreign key in the Employees

table, but this would be of no benefit to our application, since we assume that one

phone number can belong to only one employee. As such, we can leave this oneto-one relationship (along with any others) out of our database design. Licensed to [email protected]

302

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

One-to-many Relationships

The one-to-many relationship is by far the most common relationship type. Within

a one-to-many relationship, each record in a table can be associated with multiple

records from a second table. These records are usually related on the basis of the

primary key from the first table. In the employees/departments example, a one-tomany relationship exists between the Employees and Departments tables, as one department can be associated with many employees.

When a foreign key is used to link two tables, the table that contains the foreign key

is on the “many” side of the relationship, and the table that contains the primary

key is on the “one” side of the relationship. In database diagrams, one-to-many relationships are signified by a line between the two tables; a golden key symbol appears next to the table on the “one” side of the relationship, and an infinity sign is displayed next to the table that could have many items related to each of its records.

In
Figure 7.27, those icons appear next to the
Employees and Departments tables. Figure 7.27. Database diagram showing a one-to-many relationship

As you can see, one-to-many relationships are easy to spot if you have a diagram at

hand—just look for the icons next to the tables. Note that the symbols don’t show

the exact columns that form the relationship; they simply identify the tables involved.

Select the line that appears between two related tables to view the properties of the

foreign key that defines that relationship. The properties display in the
Properties

window (you can open this by selecting
View
>
Properties Window
). As Figure 7.28

illustrates, they’re the same options we saw earlier in
Figure 7.24.

Licensed to [email protected]