By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,269 Members | 1,332 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,269 IT Pros & Developers. It's quick & easy.

2qs on normalization: The 1st

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"Kostas" <no*****@noemail.net> wrote in message news:<10*************@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.


Kostas,
I once asked someone about this and he said that normalization is
good, but don't go crazy. If it works and you can get the answers you
want, go with it. More normalization is not always better. And you
have to learn where to stop. How much is enough/warranted? Disk
space is cheap, and if you index, it will be pretty fast. How much
hassle are you saving? The more tables you have, the more joins you
need, the longer your queries will take. So you have to decide on
size vs performance, among other things. If it were up to me, I'd do
something like:

CREATE TABLE Person(
PersonID Autonumber PRIMARY KEY,
FirstName TEXT(25) NOT NULL,
....)

CREATE TABLE TelNumbers(
PhoneNo TEXT(10) NOT NULL,
PersonID INTEGER NOT NULL,
NumberType TEXT(25),
PRIMARY KEY (PhoneNo, PersonID),
FOREIGN KEY (PersonID) REFERENCES Person(PersonID));

Then one person can have a zillion phone numbers (or none), multiple
cell numbers,etc. And I can query the two tables for whatever I want
with a simple join.

SELECT P.FirstName, P.LastName, T.PhoneNo, T.NumberType
FROM Person P, TelNumbers T
WHERE P.PersonID=T.PersonID
AND...

(yeah, I know, it's Oracle-flavor SQL, but it's easier to understand
sometimes than all that INNER JOIN stuff.)
Nov 13 '05 #2

P: n/a
It is perfectly understood thank you.
So I see that this trend of creating lookup-lists is not really thought of
as a good idea. I post the same question on comp.databases.theory and got
the same response from other guys.
Thanks Pieter,

Konstantinos

"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...
"Kostas" <no*****@noemail.net> wrote in message
news:<10*************@corp.supernews.com>...
Hi all,

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.