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

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

ee 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)

was associated with each department; copying all that data for each employee

would generate even more redundancy.

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.

276

Chapter 7: Database Design and Development

Figure 7.13. Related data about employees and departments

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 data in the table in Figure 7.14 that’s not likely to repeat very frequently

are the description and the station number. We want users to enter their station

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

wouldn’t gain any benefits by 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.

277

Relational Database Design Concepts