![]() |
FoxPop |
www.foxpop.co.uk |
POWERBASE TUTORIAL 14b
Building Our Second Primary Table
Below is the revised Business Table from Tutorial 14a
Field Name | Field Type |
ID | Auto |
Company Name | Text |
Address | Text |
City | Text |
Region | Relational |
Postal Code | Text |
Country | Relational |
Telephone | Text |
Fax | Text |
URL | Text |
Business Type | Relational |
Notes | Text or Memo |
Once again I am breaking this Table into several other Tables - read on and see how it all ties together.
In my database, as I showed you in Tutorial 13b, I have Contacts (people, friends, clients, etc.) and I showed you how I separated the data into several Tables to make it easier for me to manage. Well the same is true for Businesses. There are businesses that I deal with (like the computer store) where I don't have a contact name, and there are businesses where I do consultancy work where I have as many as 5 contacts. Then there are the phone numbers for them, the secretaries, beeper, voice mail and emails at work. And let's not forget my fellow consultants who could be at any client site on any given day! So I have many different numbers for them.
I hope you see where I am going with all of this! Keeping all this in mind, we shall now break apart the "Business/Company" Table into the following Tables:
![]() | Businesses |
![]() | B/C Phones |
![]() | B/C Email |
We can now see what fields these new Tables have and how they all relate.
We can start with the Business Table first
Field Name | Field Type | "Notes" |
ID | AutoNumber | This will allow us to identify this record when we use it in other Tables |
Business Name | Text | - |
Business Type | Relational to the Business type Table | - |
Address | Text | - |
City | Text | - |
Region | Relational to the REGION Table or TEXT | - |
Country | Relational to the COUNTRY Table | - |
Postal Code | Text | - |
Main Operator | Text | This is the MAIN telephone number for the company |
URL | Text | This Business's Home Page |
Notes | Memo or Text | Your choice! |
Next is B/C Phones: (this is the contact phone number(s) at the businesses)
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. |
Business | Relational
to the Business Table. The display will be: [BusinessName}+Phone:"+[Main Operator]+chr$(6)+"["+right$("0000"+str$([ID]),4)+"]" |
This is so we can display the Business Name as well as the Main Operator number and record ID. |
Phone Type | Relational to the PHONE type Table | Yes, you can use the same one as for your contacts |
Number | Text | - |
ID | Auto | - |
And finally B/C email:
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. |
Business | Relational to the Business Table. The display will be: [Business Name}+ Phone: "+[Main Operator]+chr$(6)+"["+right$("0000"+ str$([ID]),4)+"]" | This is so we can display the Business Name as well as the Main Operator number and record ID. |
email Type | Relational to the EMAIL type Table | Yes, you can use the same one as for your contacts |
Address | Text | - |
ID | Auto | - |
This might seem complex or "over kill" but I see it as having just the right amount of information in just the right place - and it all fits in the screen really easily.
The next tutorial will have the latest and (I hope) the greatest "sample database" for download, and we will discuss some other things you might want to do with the database.
PS - we are almost done!
© LJKern and FoxPop 1999