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

Figure 7.26. Creating and visualizing table relationships

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.

287

Diagrams and Table Relationships

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 Em-

ployees 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 one-to-one relationship (along with any others) out of our database design.

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 ex-

ample, a one-to-many 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.

288

Chapter 7: Database Design and Development

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.

Figure 7.28. The properties of a foreign key

289

Diagrams and Table Relationships