by Jerry Curtis

Created on: May 26, 2009   Last Updated: January 10, 2011

A well-designed database is a tremendous information and management tool. Our working world today is awash in a sea of information, and databases to a large extent keep us afloat. The principle of good database design is a crucial one, because a badly designed data base will eventually sink under the weight of the data it cannot successful manage.

My own experience with databases began as a city employee in the early 90's where I used D-Base and then moved to MS Access. As an economic development analyst, I maintained my own personal data bases on potential business property development. Rather than rely on the city's mainframe computer with its restricted access and virtually no user availability accept for the financial and permit managers, I kept my own records and made my own reports. Most of what I learned about database design was through trial and error.

During the past seven years as a consultant, I have designed a particularly lucrative MS Access data base that tracks student satisfaction surveys by semester for a California Community College vocational program. I shall use that database as one example of the steps required in database design.

Before we go to designing a database from scratch, I should mention that this article targets the MS Access user with at least some database design experience. For those who know little about this subject, I would recommend opening the MS Access data base program and running a few of the database templates that produce ready-made empty databases that can be "reverse engineered" to get an idea of good database structure.

Here are the steps for good database design:

1. Determine what you want the database to do for you.

My student satisfaction database stores numerical and text information transcribed from a student questionnaire. It produces end-of-semester summary reports showing the numerical average (5 = Highly Satisfied to 1 = highly dissatisfied) in each response on the questionnaire. It also produces a compilation of each comment students enter on the questionnaire.

Knowing the purpose of the database up front affects the design of your fields, hence, your tables. For example, my database uses numerical, but there are some text and memorandum fields. Also, since each semester's questionnaires must be averaged and sorted, the data base relies heavily on sorts and formulas in both its queries and reports.

2. Carefully design your tables, forms, queries, and reports with sufficient flexibility for future change,

• 1
• 2
• Next Page >>