Seven Steps to Good Database Design (Part 2)
To review, your database will consist of tables and fields. Microsoft Access needs a way to connect the data stored in the different tables—Calls table to Contacts table, for instance. The key to making this happen is to define a primary key in each table, which may be one or more fields that uniquely identifies each record in a table. Without a primary key, your database is vulnerable to duplicate records, which can result in inaccurate information and possibly an unusable database.
The following primary keys are defined in each table of the contact management database:
A database consisting of several tables—each identified by a primary key and fields containing data—tells you very little unless the tables are related in some way. The Contact Types table, for example, might contain the following records: Family, Friend, Company, Recruiter, and Network. In and of itself, this information may or may not be valuable to you. Only when you relate this table to a Contacts table—thereby allowing you to associate a specific name as being a family member—does this information, and this relationship, become valuable.
Once the database tables have been defined, Access provides a way for you to define relationships between the different tables. There are two main types of relationships that can be defined:
The following chart shows the three tables, primary keys (bold field in each table) fields, and table relationships in the contact management database:
After all of the tables and fields have been designed and the table relationships have been defined, it's a good idea to input some sample data to test the database and make sure it is working properly. This step is practiced in the contact management database throughout the Access XP tutorial. After you’ve entered data, create some sample queries to see if the output is what you expected. Create some sample reports to make sure these also produce similar results. Make any necessary corrections to the database design during this stage and before you enter all of your personal records. If you find any problems, correct the design.
When you're fully confident that the database is working as intended, add the records in all of the tables in your database. Identify the queries you want to run. Identify the reports you want to generate. What forms can be created to simplify data entry or data analysis? Go ahead and create—or finalize—them.
To learn more about working with Access databases, visit our Access XP tutorial.