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 that training for a new database developer, Microsoft Access comes equipped with a Database Wizard that you can use to create a number of different databases. Studying a database created by the Wizard is a perfect beginning to understanding good database design. Create some sample databases using the Database Wizard and then study its design: 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 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) is used to maintain an address book containing names, addresses, 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 very useful in a job search.
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 hardest 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 (e.g., customer address). Its information should be independent of information in other tables (e.g., customer orders). Data should not be duplicated in other tables (e.g., define a customer address in one table only). Keep the following in mind when you decide which tables are needed by your database: 1) eliminate the potential of duplicate entries, 2) don’t change the same information multiple times in different tables and 3) you want to be able to delete a record in one table (e.g., a customer order) and still maintain information in another table (e.g., customer address and phone number).
The Contact Management database used in the Access 2002 (XP) course studies three main tables: Contacts, Contact Types, and Calls.
- The Contacts table contains all the information about each address book entry.
- The Contact Types table contains the category definitions (e.g., family, friends, etc) 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 is recorded in the Calls table. Neither of these fields has a direct bearing on the address of phone number in the Contacts table. These two tables contain information on two very distinct functions.
3. What fields do you need?
Each table in the database contains information about 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 in the Contact Management database, for example, contains information about the contact, such as the fields: 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 defines 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 2002 (XP) Tutorial.