Chapter 7: Database Design and Development

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

Relational Database Design Concepts

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

Chapter 7: Database Design and Development

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 when I	1
					open documents
2	4	3	2	5	Crashes when I	1
					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), and the DepartmentID column in the Employees table, are perfect candidates for the application of a foreign key constraint. Take a look at the examples shown in Table 7.13 and Table 7.14.

278

Foreign Keys

Table 7.13. The Departments table’s primary key

DepartmentID (Primary Key)	Department
1	Accounting
2	Engineering
3	Executive
4	Marketing

Table 7.14. The Employees table referencing records from the

Departments 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

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