1. Determine the purpose of your database.
2. Determine the tables you need in the database.
3. Determine the fields you need in the tables.
4. Identify fields with unique values.
5. Determine the relationships between tables.
6. Refine your design.
7. Add data and create other database objects.
8. Use Microsoft Access analysis tools.

Determine the purpose of your database

The first step in designing a Microsoft Access database is to determine the purpose of the database and how it's to be used. You need to know what information you want from the database. From that, you can determine what subjects you need to store facts about (the tables) and what facts you need to store about each subject (the fields in the tables).

Talk to people who will use the database. Brainstorm about the questions you'd like the database to answer. Sketch out the reports you'd like it to produce. Gather the forms you currently use to record your data. Examine well-designed databases similar to the one you are designing.

Determine the tables you need

Determining the tables can be the trickiest step in the database design process. That's because the results you want from your database — the reports you want to print, the forms you want to use, the questions you want answered — don't necessarily provide clues about the structure of the tables that produce them.

You don't need to design your tables using Microsoft Access. In fact, it may be better to sketch out and rework your design on paper first. When you design your tables, divide up pieces of information by keeping these fundamental design principles in mind:

• A table should not contain duplicate information, and information should not be duplicated between tables.

When each piece of information is stored in only one table, you update it in one place. This is more efficient, and also eliminates the possibility of duplicate entries that contain different information. For example, you would want to store each customer address and phone number once, in one table.

• Each table should contain information about one subject.

When each table contains facts about only one subject, you can maintain information about each subject independently from other subjects. For example, you would store customer addresses in a different table from the customers' orders, so that you could delete one order and still maintain the customer information. Optimize general table performance

Determine the fields you need

Each table contains information about the same subject, and each field in a table contains individual facts about the table's subject. For example, a customer table may include company name, address, city, state, and phone number fields. When sketching out the fields for each table, keep these tips in mind:

1. Relate each field directly to the subject of the table.
2. Don't include derived or calculated data (data that is the result of an expression).
3. Include all the information you need.
4. Store information in its smallest logical parts (for example, First Name and Last Name, rather than Name.)

• Identify fields with unique values

In order for Microsoft Access to connect information stored in separate tables ¾ for example, to connect a customer with all the customer's orders ¾ each table in your database must include a field or set of fields that uniquely identifies each individual record in the table. Such a field or set of fields is called a primary key.

For information on primary keys, and for help in determining the types of primary keys to use for your tables, click .

• Determine the relationships between tables

Now that you've divided your information into tables and identified primary key fields, you need a way to tell Microsoft Access how to bring related information back together again in meaningful ways. To do this, you define relationships between tables.

You may find it useful to view the relationships in an existing well-designed database. To see the relationships in the Northwind sample database, open Northwind.mdb and click Relationships on the Tools menu. For information on the types of relationships, and for help in determining the relationships for your database, click . For more information on opening Northwind, click .

• Refine the design

After you have designed the tables, fields, and relationships you need, it's time to study the design and detect any flaws that might remain. It is easier to change your database design now, rather than after you have filled the tables with data.

Use Microsoft Access to create your tables, specify relationships between the tables, and enter a few records of data in each table. See if you can use the database to get the answers you want. Create rough drafts of your forms and reports and see if they show the data you expect. Look for unnecessary duplications of data and eliminate them.

• Enter data and create other database objects

When you are satisfied that the table structures meet the design goals described here, then it's time to go ahead and add all your existing data to the tables. You can then create any queries, forms, reports, macros, and modules that you may want.

• Use Microsoft Access analysis tools

Microsoft Access includes two tools that can help you to refine your database design. The Table Analyzer Wizard can analyze the design of one table at a time, can propose new table structures and relationships if appropriate, and can restructure a table into new related tables if that makes sense.

The Performance Analyzer can analyze your entire database and make recommendations and suggestions for improving it. The wizard can also implement these recommendations and suggestions.

For additional ideas on designing a database, you may want to look at the Northwind sample database and the database schemas for one or more of the databases that you can create with the Database Wizard.

Back to Top