A.2 Table Design

To understand the issues in table design, consider the Bug database described in Chapter 11. You need to know who reported each bug, and it would be very 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 that the developer working on the bug can contact that person.

You could store the identifying information with each bug, but that would be very 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 later to determine the email address for that person, you can use the PersonID to look up the Person record in the People table and that will give you all the detailed information about that person.

By "factoring out" the details of the person's address into a Person table, you reduce the redundant information in each Bug record. This process of taking out redundant information from your tables is called normalization.

A.2.1 Normalization

Normalization not only makes your use of the database more efficient, it reduces the likelihood of data corruption. If you kept the person's email address both in the People table and also in the Bug table, you would run the risk that a change in one table might not be reflected in the other. Thus, if you changed the person's email address in the Person table, that change might not be reflected in every row in the Bugs table (or it would be a lot of work to make sure that it was reflected). By keeping only the PersonID in Bugs, you are free to change the email address or other personal information in People, and the change will automatically be reflected for each bug.

Just as VB and C# programmers want the compiler to catch bugs at compile time rather than at runtime, database programmers want the database to help them avoid data corruption. A compiler helps avoid bugs by enforcing the rules of the language. For example, in C# you can't use a variable you've not defined. SQL Server and other modern relational databases help you avoid bugs by enforcing constraints that you create. For example, the People database marks the PersonID as a primary key. This creates a primary key constraint in the database, which ensures that each PersonID is unique. If you were to enter a person named Jesse Liberty with the PersonID of LIBE, and then you were to try to add Stacey Liberty with a PersonID of LIBE, the database would reject the second record because of the primary key constraint. You would need to give one of these people a different, and unique, personID.

A.2.2 Declarative Referential Integrity

Relational databases use Declarative Referential Integrity (DRI) to establish constraints on the relationships among the various tables. For example, you might declare a constraint on the Bug table that dictates that no Bug may have a PersonID unless that PersonID represents a valid record in People. This helps you avoid two types of mistakes. First, you cannot enter a record with an invalid PersonID. Second, you cannot delete a Person record if that PersonID is used in any Bug. The integrity of your data and the relationships among records is thus protected.