Designing a Database
Before you use Microsoft Access to actually build the tables, forms, and other
objects that will make up your database, it is important to take time to
design your database. Whether you are using a Microsoft Access database
or a Microsoft Access project, good database design is the keystone to creating
a database that does what you want it to do effectively, accurately, and efficiently.
Steps in designing a database:
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 in each record
5. Determine the relationships between tables
6. Refine your design
7. Enter data and create other database objects
8. Use Microsoft Access analysis tools
1 Determine the purpose of your database
The first step in designing a database is to determine its purpose 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 and they would 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.
2 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. In this respect, a table in a relational database
differs from a table in a flat-file application such as a spreadsheet.
When each piece of information is stored in only one table, you update it in
one place. This is more efficient, and it also eliminates the possibility of
duplicate entries that contain different information. For example, you would
want to store each customer address and phone number only 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.
3 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).
Compare field data types in an Access database with those in an Access project.
4 Identify the field or fields with unique values in each record
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.
5 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 in a Microsoft Access database.
You may find it useful to view the relationships in an existing well-designed database. For example, open the Northwind sample database and click Relationships on the Tools menu to see the relationships between its tables.
6 Refine your 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 than it will be after you have filled the
tables with data.
Use Microsoft Access to create your tables, specify relationships between the tables, and enter enough sample data in your tables so you can test your design. To test the relationships in your database, see if you can create queries 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. If you find problems, refine the design.
7 Enter data and create other database objects
When you are satisfied that the table structures meet the design principles
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, data access pages,
macros, and modules that you may want.
8 Use Microsoft Access analysis tools
Microsoft Access includes two tools that can help you to refine the design of
your Microsoft Access database. 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 divide 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 Microsoft Access database, you may want to look at the design of the Northwind sample database or of one of the databases that you can create with the Database Wizard.