Access XP: Seven Steps to Good Database Design (Part 1)

Lesson 17: Seven Steps to Good Database Design (Part 1)

/en/accessxp/running-and-printing-database-reports/content/

An effective, accurate, and efficient database is only as good as its design. And good design is mastered only though training and experience. This article presents seven steps that any developer—new or experienced—can follow to develop a database.

To facilitate training for a new database developer, Microsoft Access comes equipped with a database wizard you can use to create different databases. Studying a database created by the wizard is an ideal beginning to understanding good database design. Create some sample databases using the database wizard, then study its design, including tables, fields, relationships, forms, queries, and reports.

1. What is the purpose of your database?

The first step of any database design is to determine its purpose and how it will be used. What do you want the database to do? What information should it contain? What information do you want output?

The contact management database studied in the Access 2002 (XP) tutorial is used to maintain an address book containing names, addresses, and phone numbers of contacts in your address book. The database lets you group your contacts into different categories, such as family contacts or friends. Another feature of the database is the entry of notes against any phone call made to any contact. Such a history of telephone call notes could be useful in a job search, for example.

2. What tables do you need?

A relational database consists of one or more tables that are related to each other in some way. Determining which tables will be included in your design is one of the most challenging steps of the design process. Write an outline, sketch your ideas, and rework your design on paper before you create tables in Access XP. Talk to people who will use the database and understand their needs. Know the questions that should be answered by the database.

Each table in the database should contain information about a single subject (like customer addresses). Its information should be independent of information in other tables (like customer orders). Data should not be duplicated in other tables (define a customer address in one table only). Keep the following in mind when you decide which tables are needed by your database: Eliminate the potential of duplicate entries, don’t change the same information multiple times in different tables, and you want to be able to delete a record in one table (such as a customer order) and still maintain information in another table (such as a customer address and phone number).

The contact management database used in the Access 2002 (XP) tutorial studies three main tables: Contacts, Contact Types, and Calls.

The Contacts table contains all of the information about each address book entry.
The Contact Types table contains the category definitions (such as family and friends) that can be assigned to an address book entry.
The Calls table contains information about specific telephone calls.

In this database design, the date, time, and notes of a particular phone call are recorded in the Calls table. Neither of these fields has a direct bearing on the address or phone number in the Contacts table. These two tables contain information on two distinct functions.

3. What fields do you need?

Each table in the database contains information on a particular subject. Fields in a table contain bits of information, or facts about the subject matter of that table only. There should never appear duplicate fields in a table.

The contact management database contains several fields in each of its three main tables, including:

The Contacts table contains information about the contact, such as the field name, address, city, state, zip code, and phone number.
The Contact Types table contains a field that defines the relationship of the contact to you.
The Calls table contains several fields that define the date, time, subject, and notes pertaining to a call.

It would not make sense, in good database design, to include a call date or call time in a table pertaining to an address book entry. The fields, because they pertain to a phone call, make sense to appear in the Calls table only.

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