This topic is only remotely related to Excel in that if you are keeping names and addresses in an Excel worksheet, the principles discussed
below will help.
I often forget someone’s name after I am introduced to that person. So when I wanted to learn Microsoft Access, one of the first databases that
I developed was for maintaining personal and business contact infomation.
I believe Access actually has a table wizard that will create a table for contact information, but in this course we’ll do it by hand and learn
some database techniques.
This course will start with database table design techniques including dos and don’ts. Then the course will show how to develop a form and
some queries to populate the form.
I will be using Access 2002. You should be able to follow along with Access 97, Access 2000, and Access 2003.
There aren’t any prerequisites. However some knowledge of query syntax will help since I will not be going in depth into query syntax.
We begin with table design first for a good reason. Putting some thought into a good table desgin will help query and form development later on.
Before opening a new Access database let's do a bit of planning. We are going to create a table called Contacts. Let's start by listing some fields
that we want in the Contacts table.
- First Name
- Last Name
- Address
- Phone Number
Right away we have some design issues:
- Should we keep First Name and Last Name together, or keep them separate?
- What can a typical address entry look like?
- What kind of phone number should we have?
- Is this list complete?
Let's address these issues and in doing so, we'll shed some light on database table design techniques.
- Should we keep First Name and Last Name together, or keep them separate?
This issue addresses the principle of the First Normal Form of database table design - All column names should be atomic, that is, indivisible.
A person's name - "Bob Smith" - can be divided between first name - "Bob" - and last name - "Smith".
So the answer to this design question is that we keep First and Last Name separate.
A related question would be - wouldn't it be tempting and convenient to have a table row represent a family?
If a table row represented a family, we could have as first name and then last name:
"Robert, Ann, Bobby, Lizzie" "Smith"
This is a tempting idea because at a glance you could tell who was in an entire family. However, this design is flawed.
Imagine writing a query looking for "Bobby". You could not write:
SELECT FirstName, LastName FROM Contacts WHERE FirstName = 'Bobby'
You would need to use a wildcard in the WHERE clause.
Writing a query to find "Ann Smith" would not be trivial.
This related issue also is addressed in the First Normal Form Principle - do not use repeated groups of information.
"Robert, Ann, Bobby, Lizzie" are repeated groups of first names.
Another problem with design is if you have a limit on the length of a Text data type, say a limit of 50 characters. With a large family, say with even only 7
people, including comma seperators, you could easily reach the 50 character limit. Now imagine this family getting an eighth family member.
Next, we address the issue:
- What can a typical address entry look like?
Consider an address such as "55 Park Avenue South, Apt. #2". If you want to have a very flexible table design, you might want to "atomize"
the address into "50", "Park", "Avenue", "South" and have another field called Address2 for "Apt. #2". You could consider having a separate
lookup table for the street type or street suffix, i.e. "Avenue", "Road", and so on. Because if you want to maintain business contacts and do
some market research with these contacts, "50 Park Avenue" will not be in the same group as "50 Park Ave." after you sort by address.
In this course we will not "atomize" or break down the address, but it is worth considering.
Next, we address the issue:
- What kind of phone number should we have?
Many of our contacts will have home, work, mobile, and fax phone numbers. A common table design is to have a column for each type of phone.
This design is somewhat wasteful in that not all contacts will have a fax number, so in a contact report we will have room for a column that
is unnecessary for a particular person. This design breaks the First Normal Form rule - do not use repeated groups of information. It is possible
that a new type of phone can be invented or a contact could have a couple of mobile phones, say, a domestic and an international one. So how many
phone types is enough? 3? 4? 5?
The alternative is to have a PhoneType table consisting of an ID, say, 1, 2, 3, ... and a Description column, i.e. Home, Work, Mobile, Mobile 2, Fax, ...
Then in your Contacts table have a PhoneTypeID foreign key column along with the actual phone number.
Finally, we address the last issue:
- Is this list complete? And a related issue is - is it ever too late to add more columns to the Contact table?
This list is not yet complete. We still need:
- State (or Province or whatever is applicable to your country)
- Zip Code (or relevant postal code)
If you have a good table design then it is not too late to add columns while you are developing the database. On the other hand,
if you have a poorly designed table structure, it can be very difficult to add more columns.
The next lesson will be on Access table column specifications.
|