![]() |
FoxPop |
www.foxpop.co.uk |
POWERBASE TUTORIAL 11c
Rules of Normalization.
Normalizing our Contact Database
![]() | 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. |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
|
© LJKern and FoxPop 1999