/en/accessxp/seven-steps-to-good-database-design-part-1/content/

4. What are the primary keys in each table?

To review, your database will consist of tables and fields. Microsoft Access needs a way to connect the data stored in the different tables—Calls table to Contacts table, for instance. The key to making this happen is to define a primary key in each table, which may be one or more fields that uniquely identifies each record in a table. Without a primary key, your database is vulnerable to duplicate records, which can result in inaccurate information and possibly an unusable database.

The following primary keys are defined in each table of the contact management database:

• ContactID is the primary key in the Contacts table.
• ContactTypeID is the primary key in the Contact Types table.
• CallID is the primary key in the Calls table.

5. How are the tables related?

A database consisting of several tables—each identified by a primary key and fields containing data—tells you very little unless the tables are related in some way. The Contact Types table, for example, might contain the following records: Family, Friend, Company, Recruiter, and Network. In and of itself, this information may or may not be valuable to you. Only when you relate this table to a Contacts table—thereby allowing you to associate a specific name as being a family member—does this information, and this relationship, become valuable.

Once the database tables have been defined, Access provides a way for you to define relationships between the different tables. There are two main types of relationships that can be defined:

• One-to-one relationship: A record in the primary table has a single corresponding record in the related table. For example, the Contacts table that maintains addresses and phone numbers in the contact management database is related to the Contact Types table (Family, Friend, Relative, etc.) in a one-to-one relationship, meaning any given contact can have only one contact type associated with it.
• One-to-many relationship: A single record in the primary table can be linked to several tables in the related table. For example, the Contacts and Calls tables are in a one-to-many relationship, meaning many calls can be related to a single contact record.

The following chart shows the three tables, primary keys (bold field in each table) fields, and table relationships in the contact management database:

6. Does your design work?

After all of the tables and fields have been designed and the table relationships have been defined, it's a good idea to input some sample data to test the database and make sure it is working properly. This step is practiced in the contact management database throughout the Access XP tutorial. After you’ve entered data, create some sample queries to see if the output is what you expected. Create some sample reports to make sure these also produce similar results. Make any necessary corrections to the database design during this stage and before you enter all of your personal records. If you find any problems, correct the design.

7. What other database objects are needed?

When you're fully confident that the database is working as intended, add the records in all of the tables in your database. Identify the queries you want to run. Identify the reports you want to generate. What forms can be created to simplify data entry or data analysis? Go ahead and create—or finalize—them.

To learn more about working with Access databases, visit our Access XP tutorial.