FoxPop
 

www.foxpop.co.uk

 


POWERBASE TUTORIAL 11A

By Laurie Jane Kern

Designing the Contact Database


For the next few tutorials you probably will not be using PowerBase. So turn off your Fox or S5, grab a cup of coffee or other favourite beverage, and sit back and join me as we start to discuss what fields and tables you are going to have in your Contact Database. I shall also teach you about a rather dry but necessary subject called "Database Normalization".

To begin let's list the fields you had in our Address Book - Contacts Table. I will use this field list as the starting point for our discussion in this tutorial. Here is what you had:

Title
First Name
Last Name
Address Line 1
Address Line 2
City
Region
Postal Code
Country
Telephone - home
Telephone - work
Mobile
Fax
Email1
Email2
URL
Contact Type
Notes

Your Contacts table allowed you to store information about people, but was not well suited for information about businesses such as your favourite restaurant, or the tailor that you use. It wasn't well suited for listing where a person worked either. For example you might want to have the home address of a friend, and their work address. What do you do if they have more than one job and a vacation home too? So for your new Contact Database not only do you need people information, you need to add fields that are particular to businesses such as: Company Name, Company Type, Main Telephone Number, Fax, URL (don't all companies have web sites?), email address, etc.

As a teaser to that wonderful subject known as "Normalization" I have a question for you to keep in the back of your head:

Do you add these new fields to your Contacts table OR do you make a new "Business" table in your database?

To assist you in answering this question and to illustrate the possible problems, I have listed below tables that demonstrate what you might have in each instance.

 

Case 1: Personal and Business fields all in one table.


If you only have business information or only have personal information, you end up with a lot of empty fields if you design your table this way. (More on this later!).

Title
First Name
Last Name
Home Address Line 1
Home Address Line 2
Home City
Home Region
Home Postal Code
Home Country
Home Telephone
Mobile
Home Fax
Home Email1
Home Email2
Home URL
Contact Type
Company Name
Company-Address Line 1
Company-Address Line 2
Company-City
Company-Region
Company-Postal Code
Company-Country
Company-Telephone - Main
Company-Telephone - direct
Mobile
Company-Fax
Company-Email1
Company-URL
Company Type
Notes

 

Case 2: A combined Contact/Business Table.


Since some fields hold the same type of information such as Fax, URL, Telephone, Address, City, Region, etc those fields could be combined as I have done here:

Title
First Name
Last Name
Company Name
Address Line 1
Address Line 2
City
Region
Postal Code
Country
Telephone
Mobile
Fax
Email
URL
Contact Type
Notes

Since you have combined the functionality of certain fields - address fields for example, then a record would be for either a person or for a business and you could use the contact type as a way to group, or filter, the information. If you wanted to have both a home address and work address for someone, you would end up entering two records for that person.

 

Case 3 Separate People and Business Tables.

Business Table   People Table
     
Company Name   Title
Address Line 1   First Name
Address Line 2   Last Name
City   Address Line 1
Region   Address Line 2
Postal Code   City
Country   Region
Telephone -Main   Postal Code
Telephone - Direct   Country
FAX   Telephone - home
Email   Mobile
URL   Fax
Business Type   Email1
Notes   Email2
    URL
    Contact Type
    Notes

As you can see, you can have people in your people table, and you can have their home addresses, and vacation homes, and you have businesses in your business table. The only thing lacking here, is a way to associate a contact/person to a business/company. This situation can be resolved by adding a field (relational type) to the people table that relates back to the business table. The relational field would be in the people table because a person works at a business. Assume you have two friends who work at the same company, you would enter the company once but it would be related to two different people. How you do that will be discussed later. (We have other things to discuss next!)

Database Normalization
So now we come to the dreaded subject of Normalization!

Normalization is just a fancy term for the process of evaluating your database design against a whole lot of rules, and these rules can assist the DBA to design the database to operate efficiently, but what is efficient? Well, in a large database, where lots of rows can be returned from a search (also known as a query), it takes time to return that data, so efficiency here might mean speed. In other cases efficiency means reducing data redundancy - having to maintain data in multiple places takes time. It is situations like this that have an impact on how the database will be designed. To be perfectly honest, there are times in the real world that DBAs will throw out the rules of normalization, because the database may be "efficient", but it is a pain in the you know what to work with it.

Now, will the PowerBase database operate more efficiently? In terms of speed, I personally don't know that answer. Can you make PowerBase, or to be more exact, can you make your Contact Database efficient in terms of data redundancy? That you can do something about. In our next lesson we shall look at the Rules of Normalization and try to apply some, since they can help you make your Contact Database "efficient". See you all then!

© LJKern and FoxPop 1999

 

[Previous] [Index] [Next]