Table Design

To understand the issues in table design, consider a database for recording orders (such as the AdventureWorksLT database used in this book). You need to know who placed each order, 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 order, and in that detail page you offer the email address and phone number of the customer so the salesperson working on the order can contact that customer in case of a delay or stock issue.

You could store the identifying information with each order in an Orders table (named SalesOrderHeader in the database, for reasons we’ll mention in a minute), but that would be inefficient. If John Doe placed 50 orders, 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, the customer details are kept in a second table, called Customer, in which each row represents a single customer. In the Customer table, there will be a column for the CustomerID. Each customer will have a unique ID, and that field will be marked as the primary key for the Customer table. A primary key is the column or combination of columns that uniquely identifies a record in a given table.

The Orders table will use the PersonID column as a foreign key. A foreign key is ...

Get Programming ASP.NET 3.5, 4th Edition now with the O’Reilly learning platform.

O’Reilly members experience live online training, plus books, videos, and digital content from nearly 200 publishers.