FoxPop
 

www.foxpop.co.uk

 

 

POWERBASE TUTORIAL 11c

by Laurie Jane Kern

Rules of Normalization.

 

Normalizing our Contact Database
We left off last time, looking at the first rule of Normalization and you learned a new term, Entity. This lesson will discuss the Second Normal Form. Since the last lesson was so long, I shall attempt to keep to this one short and we can all go home early :0)

Second Normal Form (also known as Rule Number Two):
The second normal form stated that every field in a table should relate to the Primary Key - in its entirety.

Ok, that was simple but really what does this rule mean? Well to put it another way, every field should supply additional information about the record the primary key serves to identify. Every field in the contacts table should describe something about the contact with that contact key, no more and hopefully no less. This is very important as the table has a composite key (a primary key composed of more than one field). So if you have a composite key all the other fields in the record must relate to all the fields comprising that key, NOT just to one field or some of the fields.

Here is an example: Pretend you have an Order form for buying software from Purple Software. You have tables that hold this information in a database. Those tables are Order Header and Order Detail . The Order Header is to hold unique in formation about the Order, this information comes from the top part of the order form. The Order Detail holds the information about each item being ordered that is usually the lines where you write in item number, description. etc.

When you buy stuff from Purple, don't they usually assign the order and order number? Well in the order header table you probably have a primary key which is this Order Number. Now what other information would you have on the Header record? Information about the Customer, the order date and the shipping method? Yes, but remember each field must supply additional information the primary key represents. Ok, the order date is additional information, but what about the customer? Would you have lots of fields about the customer, first name, last name, street address, city, country? No, the information about the customer: Name, Address, Phones, etc. is not really additional information about THIS order. These fields provide information about the customer, but what about the Primary Key that identifies THIS customer?

Is this specific to this order and thus provides the additional information requirement? YES!! So the Order Header would have a field to hold the Customer ID, because that is additional information about this order - It relates back to the entity of "Who ordered it".

Let's now move on to testing your three cases to see if they meet the second rule of normalization. Check carefully - But remember, you learned last week that each of your cases does not have a primary key, but assume you do and in each case it is an ID field (an auto number field will generate a unique ID each time you add a record). Using that assumption now look at how the data in each table relates to the purpose of the table.

 

Case 1: The name, address and some of the phone fields those are fields that provide additional information about a contact. But what about the company information? Not just the company name but the address, telephone numbers, web addresses? That data could be used for more than one contact and the fields about the company describe the company (attributes of the company) so they violate the second rule of normalization.

Case 2: If you use this table for either a Contact or a Company there are fields in the table that are not used because they are specific to the other type of data. If you fill in company name the fields; Title, First Name, Last Name are attributes about a person. These fields will then violate the second rule!

Case 3: Well, we have a winner here! Each table contains information only about either a company or a contact. Forget for a moment that this case failed the first normal form test all of this will be addressed (oops, sorry for the pun!) when do your final design.

Keep all of this in the back of your mind. Next time we move on to the Third Normal Form. Soon you will actually design your database tables, and even break some of the rules (I will explain why later!)

Lets all put away our books and go out and play! See you next time!

 

Case 1:
Personal and Business fields ALL in one table
Case 2:
A combined Contact/Business Table
Case 3:
Separate People and Business Tables.
Title
First Name
Last Name
Home Address Line 1
Home Address Line 2
Home City
Home Region
Home Postal Code
Home Country
Home Telephone
Mobile
Home Fax
Home Email1
Home Email2
Home URL
Contact Type
Company Name
Company-Address Line 1
Company-Address Line 2
Company-City
Company-Region
Company-Postal Code
Company-Country
Company-Telephone - Main
Company-Telephone - direct
Mobile
Company-Fax
Company-Email1
Company-URL
Company Type
Notes
 
Title
First Name
Last Name
Company Name
Address Line 1
Address Line 2
City
Region
Postal Code
Country
Telephone
Mobile
Fax
Email
URL
Contact Type
Notes
 
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
     
     

© LJKern and FoxPop 1999

 

[Previous] [Index] [Next]