|These are the basic steps in designing a
- Determine the purpose of your database.
- Determine the tables you need in the database.
- Determine the fields you need in the tables.
- Identify fields with unique values.
- Determine the relationships between tables.
- Refine your design.
- Add data and create other database objects.
- 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
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:
- Relate each field directly to the subject of the table.
- Don't include derived or calculated data (data that is the result of an expression).
- Include all the information you need.
- 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 .
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
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