Microsoft Office Tutorials and References
In Depth Information
Designing a Database
different value in each record. If one field is different for every record in the
table, you’ve found your primary key field.
Autonumbering your records
Well, okay, you may not find a unique field. It happens. Tables that list
people (such as the Customers table) can pose such a problem. Some people
have the same name; family members or roommates can share an address
and phone number. Most businesses end up creating and assigning unique
numbers to people to prevent this problem.
For privacy reasons, don’t even think about asking for anyone’s Social
Security number. Make up your own customer numbers!
Fortunately, assigning each record in a table a unique number is easy
in Access: Just add an AutoNumber field to the table. Access numbers
the records as you enter them. In your bookstore system, you can add a
Customer Number field to the Customers table.
The advantage of using an AutoNumber key as the primary key field is
that you can’t change its values. After you relate two tables by using an
AutoNumber field as the primary key, breaking the relationship between the
tables later, if you have to edit the value of the AutoNumber field, is
For the Orders table, you can use Customer Number instead of the
customer’s name to identify who places the order. Because one customer may
make several purchases, however, you still don’t have a unique key for the
Orders table. One solution is to use a combination of fields as the primary
key. How about using the Customer Number and Purchase Date fields
together as the primary key? This solution works fine as long as a customer
doesn’t place two orders on the same day. (Hmm, that may not work either;
people sometimes forget to buy everything they need and come back to the
store later for one or two more items.) Instead, add an AutoNumber field to
this table to provide a unique Order Number.
Seeing why two key fields may be better than one
Sometimes, using a combination of fields works fine. In the Order Details
table, you’d better add a field for the Order Number so that you can get
immediate access to whatever order contains these items. You don’t need
to add a Customer Number field in this case; after you identify the Order
Number, Access can look up the Customer Number and other customer
The Order Number field doesn’t uniquely identify records in the Order
Details table, because one order can (and a business owner would really
love it to) include lots and lots of products. Use a combination of the Order