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

Thank you for your informative answers. I read them all very carefully and I understand what you are saying. It is interesting for me to see that the second design was logically a worse choice because I thought from a couple of example cases I had found that this is the theoretical preferred design.

1. I used the PhoneType entity because I was concerned for string errors of the type "Cellular" vs. "Celular" and so forth, whereas if those values were to be fixed a-priori in a separate table this wouldn't occur.
2. As for using phoneID as an identifier instead of the actual phone number, I did it because I read articles on the debate of semantic vs. artificial identifiers and again got the impression that artificial poses less threats to the development of a database.

But thank you very much, I really appreciate it. It is exciting and frustrating at the same time, that even the simplest databases have so many parameters to be decided and alternative designs that are possible!

Konstantinos

"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
>
>
>
>
>
Received on Fri Oct 15 2004 - 18:20:53 CEST