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

"Kostas" <noemail_at_noemail.net> wrote in message news:10mult27ildsc26_at_corp.supernews.com...
> 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
> 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).
>
> 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?
>
> Many thanks to anyone who can clue me in,
>
> Kostas
>

If you store the complete phone number (including international and local codes), you will have a unique ID, so no need for an artificial PK. You would need to store the seperators as well, in case you have otherwise confusing number overlaps. You would need only two columns, phone_number and phone_type. Umpteenth normal form.

Ex:

011-44-555-1234
country code-local-exchange-number

By storing the hyphens, you can accommodate any country's numbering method. If you need to sort by country code, or any other part, then add a column for that part, or parse it out each time. Received on Fri Oct 15 2004 - 15:52:38 CEST