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, but with sufficient data reliability that any change will not adversely affect information already entered.
Knowing what you want your database to do and having a vision of its final product or products dramatically affects its upfront design. For this reason, in my design consultation with my customers, I emphasize the end product's impact on design time. I make it clear that if the customer wants additional reports and products not already agreed upon, those new functions could require design changes and more expense.
3. Keep meticulous database design records for every object you create.
As you make your tables, keep a note of your field formats and special rules. When you create a new form, keep track of what table or query that form is based on. For relational databases (two or more tables connected by a common field), keep design notes on what tables and fields are involved. Do the same with reports and any macros you design to navigate your database.
It is tempting to whiz through database design and to fix problems on the fly. However, all this whizzing and fixing in the end will cause design problems down the road, especially during design additions or modifications. MS Access has built-in tools that prevent damage to the database, and you can even run the database documenter to troubleshoot. However, there is simply no substitute for journaling your work.
4. Test the database design thoroughly.
Enter only a few representative records. Test your forms, queries, macros and reports. Even the best designed database has bugs. The time to get bugs fixed is before the customer enters tons of records.
In the end good database design is like good design for a bridge. Before the first rivet is put in place, the designer knows everything that needs to be known about the work the bridge needs to do: its traffic load, how it must sit on the river bed, etc. Likewise, in database design, before the first bit of data is entered, the database designer needs to know everything the database needs to produce: the reports and management information that makes our working day so much easier.