FoxPop
 

www.foxpop.co.uk

 

 

POWERBASE TUTORIAL 12

By Laurie Jane Kern

Designing Lookup Tables for our Contact Database.

 

Finally - we are going to start to design and build some tables!

As we look at the fields we want in our database (see the list below - actually we may add fields later and break these two tables into a few more...) I want to show you how certain fields should be based upon "lookup" tables.

In a Primary Table such as People or Businesses, there are fields that have values that provide information about the current record. Take for example: Title, Contact Type, Business Type, and Gender versus FirstName, LastName and City. The second set of fields will "always" be different from record to record. The first sets of fields are usually different from record to record but the values you use are most likely a predefined set. Using our Title field, the most frequently occurring values are Mr., Miss, Mrs., Ms, and maybe Reverend and a few others. As you can see, these values are relatively static - you might add a new value every once and a while but not very often. The fields in the primary Table that have this type of data should use a value from a lookup table. This is also more convenient - you don't  have to type the "value" over and over again.

The major point about a lookup table is that these are tables that are used for informational purposes. That is why they are called lookup tables - they are tables but you use them for mostly looking up a value. [See Tutorial 10 for making Lookup Tables]

Below you can see which fields I have identified that should be lookup tables - they have RED BOLD text. I do not have a gender field in MY contact database, but if you want a Gender field, then that too would be a lookup table. 

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

Keeping in mind what fields should be lookup tables, I must now take you back to the Tutorial 11 series   where we learned the Rules of Normalization. Wait - don't run away! This is not going to be so hard.... I want to walk you through the design of ONE lookup table, so you can see how easy it is going to be. We are going to use the Title field so here we go!

The Rules of  Normalization state that each record should be unique [Tutorial 11b]. So let us look at what our records are going to be in the TITLE table: Mr., Mrs., Miss, Ms., and Dr. Would we have Mister and Doctor as well? They are spelled differently but they mean the same thing, so I would say no! Next, the Rules of Normalization state that we should have a primary key, that will help make each record unique [Tutorial 11c]. Well, we use one field named ID and have it be an autonumber and the second field will be the Type or Title (and is a text field) and that is where we have our title values entered. But since PowerBase does not "invoke" or enforce primary keys and by being careful in our entries, I don't think we need the ID field. We can state that the Type/Title field is our primary key since we can make sure that each record is unique.  Looking ahead, the field in the primary table that "was the text" field for the Title would now be a relational field - but more of that later! Using the pattern of Tutorial 10, you can now make a Lookup Table to the new Contacts database.

 tut12.gif (12492 bytes)

So! now that we have a lookup table for our Titles, I leave it up to you to figure out what our other lookup tables might be. If anyone has ideas for other lookup tables send me email and I will discuss your ideas in the following tutorials.

Taking the Plunge?

You CAN start now to design the new Contacts database, with the two initial Tables, one for Business, and one for People. [You can refer to Tutorial 3 for how to construct the database]. Then add the Lookup table, as the screenshot above shows - BUT don't start adding your important data yet! We still have some more decisions and changes to make. Use this as an exercise, an experiment; not as the finished thing!

Next time we will work on our first Primary Table.

© LJKern and FoxPop 1999

[Previous] [Index] [Next]