Community
Access Table Design
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?
- Should we keep First Name and Last Name together, or keep them separate?
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 or issue 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, "Robert, Ann, Bobby, Lizzie" and as last name, "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.
Imagine creating an address label for "Ann Smith"!
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 gaining an eightth 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.
If you know that in your particular case you will have a fixed number of phone types then you can use the first design. For this lesson we will use home, work, mobile and fax numbers. Remember to make columns for extension numer for the work 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.
Page Author
- Avatar
- Name
- geof
From Here You Can…
Information
- 570 Views
- 0 Comments
Most Recent Related Content
- Lesson
- Avatar
- Title
- Access Contact Table Specifications
- Body
- (This lesson includes a link to the ContactsDB.mdb database, as it stands at ...
- Author
geof
