Programming ASP.NET, 3rd Edition

Table Design

To understand the issues in table design, consider the Bug database described in Chapter 10. You need to know who reported each bug, and it would be useful to know the email address, phone number, and other identifying information about each person as well.

You can imagine a form in which you display details about a given bug, and in that detail page you offer the email address and phone number of the “reporter” so the developer working on the bug can contact that person.

You could store the identifying information with each bug, but that would be inefficient. If John Doe reported 50 bugs, you’d rather not repeat John Doe’s email address and phone number in 50 records. It’s also a data maintenance nightmare. If John Doe changes his email address and phone number, you’d have to make the change in 50 places.

Instead, you’ll create a second table called People, in which each row represents a single person. In the People table there will be a column for the PersonID. Each person will have a unique ID, and that field will be marked as the primary key for the Person table. A primary key is the column or combination of columns that uniquely identifies a record in a given table.

The Bugs table will use the PersonID as a foreign key. A foreign key is a column (or combination of columns) that is a primary (or otherwise unique) key from a different table. The Bug table uses the PersonID, which is the primary key in People, to identify which person reported the bug. If you need to ...