Chapter 5: Modifying Your Table Design

Overview

No matter how carefully you design your database, you can be sure that you’ll need to change it at some later date. Here are some of the reasons you might need to change your database.

• You no longer need some of the tables.

• You need to perform some new tasks that require not only creating new tables but also inserting some linking fields in existing tables.

• You find that you use some fields in a table much more frequently than others, so it would be easier if those fields appeared first in the table design.

• You no longer need some of the fields.

• You want to add some new fields that are similar to fields that already exist.

• You discover that some of the data you defined would be better stored as a different data type. For example, a field that you originally designed to be all numbers (such as a U.S. ZIP Code) must now contain some letters (as in a Canadian postal code).

• You have a number field that needs to hold larger values or needs a different number of decimal places than you originally planned.

• You can improve your database design by splitting an existing table into two or more tables using the Table Analyzer Wizard.

• You discover that the field you defined as a primary key isn’t always unique, so you need to change the definition of your primary key.

• You find that some of your queries take too long to run and might execute more quickly if you add an index to your table.

Note 

The examples in this chapter are based on the tables and data in Housing.accdb and Contacts.accdb on the companion CD included with this book and the Contact Tracking database you built in Chapter 4, “Creating Your Database and Tables.” If you did not create the Contact Tracking database, you can find ContactTracking.accdb in the sample files that you can use to follow along in this chapter. The results you see from the samples you build in this chapter might not exactly match what you see in this book if you have changed the sample data in the files. Also, all the screen images in this chapter were taken on a Microsoft Windows Vista system with the display theme set to Blue, and Use Windows-Themed Controls on Forms has been turned on in the sample databases.

This chapter takes a look at how you can make these changes easily and relatively painlessly with Microsoft Office Access 2007. If you want to follow along with the examples in this chapter, you should first create the Contact Tracking database described in Chapter 4.

Note 

You might have noticed that the Contacts table you defined for the Contact Tracking database in Chapter 4. is quite different from the tblContacts table in the Conrad Systems Contacts database on the companion CD. In this chapter, you’ll modify the Contacts table you built in Chapter 4. so that it is more like the one on the companion CD. You’ll also learn how to use the Table Analyzer Wizard to help you normalize an existing table that contains data from several subjects.