applied tips : january 2009
microsoft access : the dos and don'ts of tables
Getting ready to create a new database, or improve an existing one? Let's review some of the general rules that go into database design, particularly with Microsoft Access (any version).
Put Like Records into the Same Table
One of the tenants of database design is to group like records into the same table. Sometimes, we use the spreadsheet-to-table analogy too often and create multiple tables that are basically storing the same types of records.
For example, if we are storing transactions it might be tempting to create two tables: one for Transactions 2008 and a new one for Transactions 2009.
This concept causes many difficulties down the road and focuses us to reconfigure queries and reports, giving us way more work than necessary.
The recommended solution is to put all of the data into the same table, using a field such as TransactionDate to grab only the data we want. (Remember we can easily filter data using queries in any number of ways.)
Naming tables is a fairly easy task, but a few rules should be considered:
- Table names should never contain spaces or special characters. Stick to letters and you'll be safe.
- Table names should clearly identify what type of "collection" the table contains. If each record represents one customer, call the table "Customers". If it contains orders, call it "Orders".
- Keep table names as simple as you can, and make sure the name is plural since it represents a collection of objects.
Renaming a table can be a frustrating experience, especially if your database already has forms, reports, queries, and even more especially if you've created Visual Basic code to enhance your application. Get the name right the first time and avoid future headaches.
Splitting Fields into More Fields
If a single field contains multiple pieces of data, such as the person's first and last name, you should probably split it into two fields before you get a lot of data entered.
The reasoning behind this is that you might need only one portion of the field sometime in the future. If you were creating a merge letter, and wanted to say "Dear David", you would have to have David in its own field. While there are programmatic ways to split fields as an afterthought, it's a tremendous amount of work.
Another example is a mailing address or city/state/zip combination. Even phone numbers with extensions are often split into separate fields to ease data entry and format the data consistently.
Fields Commonly Forgotten
Here's a few contact fields that are often forgotten:
- Address Line 2 (or even 3)
- Country (which you can default to "United States")
- PhoneWork (or Cell or Fax or Home, etc)
- PhoneWorkExt (for their extension, so that you can enforce an Input Mask on the regular phone number and leave this field unrestricted)
- Notes (give your user a generic Memo field for all miscellaneous entry, so that they aren't forced to put it somewhere else)
- Active (default to "Yes", then instead of deleting inactive records, mark them as not being active)
- Birthday (instead of just their Age, which always changes)
Although Access will allow you to name a field almost anything, there are some general conventions to follow:
- Field names must be unique within a table, but can be duplicated across tables. For example, both the Charges table and Contacts table can contain a field called "DateCreated". Just be careful that you don't confuse them if you ever create a query that combines both tables.
- Field names should consist only of letters, numbers, and hyphens. Symbols such as the ampersand (&), underscore, percent sign, and dollar sign should not be used. Spaces, also, should be avoided.
- Field names should only start with a letter.
- Letter case is irrelevant, so uppercase letters can be used in place of spaces. For example, the field name "StartDate" is easier to read than "Startdate".
- Keep field names short, but not so short they are difficult to decipher.
Following these guidelines will help create a database that scales easily and provides compatibility with the built-in programming language Visual Basic.
Data Types and Restrictive Properties
Especially when starting a new database, be sure to review each of the Data Types and their Properties to force data entry to be as correct as possible.
For example, changing a field from Text to Date/Time requires that the user enter a valid date (in any format).
Properties like "Required", "Input Mask" and "Indexed" can also help you restrict or conform data entry and avoid problems in the future.
Using a Lookup (to create a Pull-Down menu) is another way to add functionality to the database and enforce proper data entry.
While improving your database, ask yourself what you can do today to avoid future problems. What features might you need in six months or a year that you should start to make available today?
If you think that it's likely you might someday need the email addresses of your customers, even if you're not sending them email today, create the field so that it can be populated when available.
Creating a database is a lot like creating a house, and you can't do that until you've got a sufficient idea of what the house is for. Even then, without blueprints and a firm idea of what you're building, you wouldn't want to start laying concrete or raising walls.
The best databases are those with lots of forethought, without rushing into things.
schedule a class or get consulting on access
Request an on-site Access class from Applied Office or get some helpful consulting. Learn more here