MS Access Databases: Tables are the Foundation

MS Access is a powerful database application that, unlike the Excel spreadsheet, the new user needs to master a learning curve and think globally. The global thinking involves considering your “end product” even before you enter your data. For this reason, database professionals and consultants devote as much time planning database “output” (what reports or information the database will produce) as they do with “input” (how the data base will accept and process the data).

Experienced database designers know that good planning and data design must happen before the data is entered into the database. A grouped report from a large database that is based on a grouping of zip codes, for example, cannot be conveniently generated unless the database was designed with a separate field for the zip code. Likewise, a database design that does not include some level of “quality control” at the data entry level will eventually result in a cluttered database of questionable value.

The quality control begins with the thoughtful design of your MS Access database table. A table, in turn, is only as good as the fields we design that make up the individual records, which are our separate groupings of vital information on a person, product, or other item tracked, grouped and reported by our data base. Think of a database table as a sort of warehouse for your information. We keep our store of information orderly by designing our “containers” (fields and records) in a consistent way that prevents outside “contamination” (i.e., by preventing bad data from being entered).

In database table design, we consider both the types of fields we need and the properties (categories) and entry rules we need to keep entry errors to a minimum. In a customer database, for example, we would maintain only personal information (name, address, phone, etc.). We would also attach data entry “rules” to each new field. (For example, is it permissible to leave a field empty? What happens when a zip code is entered with fewer than the required five initial digits?) Would accessing a “look-up” table be advisable as an aid to more accurate data entry? These considerations are especially important if persons unfamiliar with database design are entering data into the database.

So there are a ton of considerations that go into the design of your database table (its records and fields). The temptation in using any computer application is to jump right in and make it work. The quick-start approach works fine with word processors and spreadsheets, but can lead to serious problems if your database is badly planned and designed. A poorly designed table can be repaired through global editing or adding or modifying fields.

Unfortunately, wholesale modification to one table can lead to unexpected disasters, as forms, queries, and reports based on the original table design no longer work as designed. Small wonder that database design professionals often include extra charges for redesign.

Thinking about designing a new database? MS Access has a number of handy templates ready for the new user. If your needs exceed what these templates offer, however, you may need to resort to the “stubby pencil” method and design your database on paper. Consider first what you want your database to do (the reports, data retrieval and groupings, etc.). Then design your tables that will be the source of everything.