![]() |
FoxPop |
www.foxpop.co.uk |
POWERBASE TUTORIAL 13 b
Building our First Primary
Tables
Below is the revised People Table from Tutorial 13a where we left off the last time.
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 |
I am now going to explain how and why I am breaking this Table into several Tables. Remember this is not the only way to set up a Contact database; this is how I have done it, to fit my personal and work life. So here we go!
In my database I have friends, family members, people I work with, people I consult for (or have in the past). There are also companies too, but that is a whole other set of Tables which we shall discuss later. There are people I correspond with via email, but have never met, nor do I have their addresses. In addition to this, I have contacts who have NO email addresses and some with 3. Then there is the telephone - 2 lines at home, mobile (car) phone, beepers, cell phones and voice mail as well! There are the other consultants I work with and we could be at any of 1, 2, 3 or more client sites - some I do work at and others I don't.
I personally do not like having a record in a Table (as shown above) and I then fill in only 2 or 3 fields. And what do I do when I don't have enough fields? Detailing my own database here will help to explain why I have broken the People/Contact Table into smaller Tables with each Table focused on "a specific category of information" . Here are the Tables we are going to build:
![]() | People |
![]() | Phones |
![]() | Personal Addresses |
![]() |
I expect that now you are now wondering what fields make up each of these new Tables and how do they all relate?
Let's start with the People Table first
Field Name | Field Type | "Notes" |
ID | AutoNumber | This will allow us to identify this record when we use it in other Tables |
Title | Relational to the TITLE Table | |
FirstName | Text | |
LastName | Text | I don't have a lot of contacts who give me there middle name or initial so I do have that field in my Table |
Contact Type | Relational to the CONTACT type Table | |
Gender | Relational to the GENDER TYPE Table | Hmmm, do we need to make a new lookup table? |
Birthday | Text | |
Age | Calculated: see tutorial 8 for a choice of equations | This is an optional field, I do not have it in mine but you might want to have it. |
Notes | Memo or Text | Your choice! |
Next is Personal Addresses Table:
Field Name | Field Type | "Notes" |
Contact | Relational to the People Table. The display will be: [FirstName}+" "+[LastName]+chr$(6)+"["+right$("0000"+str$([ID]),4)+"]" | This is so we can display the full Name as well as the record ID. You can also change it to have the last name before the first name. |
Address | Text | |
City | Text | |
Region | Relational to the REGION Table or TEXT | If most of your "contacts" were in the USA and or Canada, then the lookup Table would contain the State and Territory Names. If you are in the UK this could be a listing of the Counties or even "WALES" and "SCOTLAND". But is you are like me, where you have addresses from around the world, it could be a text field or you could have counties, states and Canadian territories. |
Country | Relational to the COUNTRY Table | |
Postal Code | Text | |
Notes | Memo or Text | Your choice! |
Home Page | Text | A Home Page is an address of sorts, don't you agree. |
ID | Auto |
Now for the Phones Table:
Field Name | Field Type | "Notes" |
Contact | Relational to the People Table. The display will be: [FirstName}+" "+[LastName]+chr$(6)+"["+right$("0000"+str$([ID]),4)+"]" | This is so we can display the full Name as well as the record ID. You can also change it to have the last name before the first name. |
Phone Type | Relational to the PHONE type Table | Oh! We need to make a new lookup table! |
Number | Text | |
ID | Auto |
And finally the Email Table:
Field Name | Field Type | "Notes" |
Contact | Relational to the People Table. The display will be: [FirstName}+" "+[LastName]+chr$(6)+"["+right$("0000"+str$([ID]),4)+"]" | This is so we can display the full Name as well as the record ID. You can also change it to have the last name before the first name. |
Email Type | Relational to the EMAIL Type Table | Oh! We need to make a new lookup table! |
Address | Text | |
ID | Auto |
What about the two new lookup Tables - Email Type and Phone Type? The Table layout will be very similar to the other lookup Tables we created. For now I leave it up to you to determine what TYPES of phone and email values you might want - I shall reveal my choices in the sample database that will be available for download with the next tutorial. I will also have data in the sample database so you can see how it all works. See you soon.
© LJKern and FoxPop 1999