![]() |
FoxPop |
www.foxpop.co.uk |
POWERBASE TUTORIAL 13a
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 TableHere 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 |
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 |
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