FoxPop
 

www.foxpop.co.uk

 

 

POWERBASE TUTORIAL 13a

by Laurie Jane Kern

Analyzing Our First Primary Table.

 

In our last tutorial we discussed lookup Tables and as I promised, I have created the first of our new Sample databases. The sample database at this point only contains the lookup Tables we discussed in Tutorial 12 [Editor's note - the Sample databases are zipped, and include two versions, one for the Geofox screen, and one for the smaller S5 screen]

So, now on to our Primary Table

Here are the fields we indicated that we might want in our "People" or "Contact" Table.

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

I am now going to go over the Rules of Normalization (as a bit of a refresher) for this Table and we shall make some changes and additions.

First Rule: The first normal form dictates that every instance of an entity referenced in a Table will be unique and that all fields in the Table contain data that is non-decomposable

Is every record or entity unique? I know we don't have "data" or records in THIS Table yet, but think about where you keep your contacts now. Is it possible that you might have two people with the same name? And while we are at it, do we have a primary key? We could say that our primary key is a composite key made up of the FirstName and LastName fields, but does that mean the key will always be unique?  I suggest we add an ID field and make this our primary key. (We will use this later - hint! )

Are the fields decomposed? Well, I think that we have met the non-decomposable part. If you look at every field you can see that none of them could be broken down further. If you wanted to you could combine Address1 and Address2 because they both apply to the address. It is just that some people have longer addresses than others do.

Second Rule: The second normal form states that every field in a Table should relate to the Primary Key - in its entirety.

This rule will be satisfied provided a work fax number is not put in the fax field or business email addresses in either of the email fields. Remember that the People Table deals with people (and their personal information) not WORK

Third Rule: The third rule states that any non-key field in a Table must relate to the Primary Key of the Table and not to another field, that is not a Primary Key.

This rule is satisfied, we don't have fields that relate to another field.

Let's now tie in what we learned in Tutorial 12 and use our new lookup Tables. The following fields will all be relational fields that point back to the lookup Table named accordingly. These fields are: TITLE, CONTACT TYPE, COUNTRY, and REGION.

Here is our revised People Table:

People Table Field Type
ID Auto Number
Title Text
First Name Text
Last Name Text
Address Text
City Text
Region Relational
Postal Code Text
Country Relational
Telephone - home Text
FAX Text
Mobile Text
Email Text
Email2 Text
URL Text
Contact Type Relational
Notes Memo

That's it for tutorial 13a. tutorial 13b is going to put a spin on the People Table design as we are going to split this Table into several others. Why you may ask? Well… I want the data to be segmented by the type of information contained in the Table. I will explain this reasoning further next time, AND if you don't like my design you can keep the Table design as it is now - but you might want to follow along because you could find this interesting.

See you soon.

You can download Sample Database 12 here

© LJKern and FoxPop 1999

[Previous] [Index] [Next]