Re: I have read tons of theory...but still...one question

"Kostas" <noemail_at_noemail.net> wrote:

>Hi all,
>
>I strived understanding the concepts of all normal forms up to the
>domain-key normal form but there are some issues I still am unsure of, and I
>will describe the first one in this post.
>(it looks so simple that there has to be an answer)
>
>Suppose a relation that holds information about Phone Numbers:
>PhoneNumber (PhoneID, ActualNumber, PhoneType, Comments)
>
>We have 4 fields.
>1. PhoneID ==> a system-generated identifier
>2. ActualNumber ==> the actual phone number
>3. PhoneType ==> determines the type of phone and can have values only 3
>values which are {Home, Work, Cellular}
>and

     If I have a cell phone for my personal use and one supplied by my employer, how do I encode it? I am not likely to give out my personal cell phone number to my employer.

>4. Comments ==> comments for a specific phone number.
>
>I believe this first relation is in Boyce/Codd Normal Form.
>
>Obviously, however, there is some redundancy in having the three values of
>the PhoneType attribute repeating for thousands of records containing phone
>numbers. So I create another table
>PhoneType (PhoneTypeID, PhoneType).

     No. They are the values for different keys. Two accounting transactions in a database can have the same amount, but be for quite different things. This is the same sort of situation. This is not redundancy as per the normal forms.

>Now I have the two tables
>Phone (PhoneID, ActualNumber, PhoneTypeID(foreignkey) Comments)
>PhoneType (PhoneTypeID, PhoneType)
>
>This allows me to get rid of the redundancy of the first design because I
>only need to store the 3 phone types once in the second table.
>My qs is, in doing this what normal form am I enforcing? Is this type of
>decomposition an example of enforcing the elusive 5th normal form?

     No. It is a case of overcomplicating your design. Take another look at the normal forms.

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:

     I have preferences.
     You have biases.
     He/She has prejudices.