[AccessD] Slow day - normalization discusion

I would say that it would depend on how you are going to use the phone
number.  Having the area code separated only makes sense if you are
actually going to query against the area code.  The first 3 digits of
the number itself can also be (sometimes) linked to specific areas, at
least they used to be. Ie, the Area Code used to define specific areas
that could include multiple cities and towns.  The first three digits
where then associated with a smaller area.  However, I don't think that
really applies anymore, especially in this age of cell phones.

So if you aren't going to query against those first three digits, then I
don't see any reason to separate it into another field.  

Another reason to split data up is for space considerations.  However,
in this case, you aren't really saving space by splitting the number OR
the area code. 

Area code (three digit number):  Storing this as text would be 3 bytes
(actually 4, Jet prefixes the data with a length byte).  Storing this as
a Long Integer Key, that is going to be 4 bytes.  Storing it as a number
would also be 4 bytes (just wouldn't require a join to get the actual
Area code).

The same would apply to the 3 digit prefix, but if you look at the phone
number itself, 7 digits, as text, that would require 8 bytes.  But as a
number, it would only require 4 bytes (again, long integer).  The quirk
with storing it as a number, is that you are going to be doing data
conversions when trying to display it in a 'format'.  Not that it's
difficult to do, in fact, Access would probably do it automatically, but
internally, it is going to be converting the number to a string
constantly.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Bartow
Sent: Wednesday, June 18, 2008 10:35 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Slow day - normalization discusion

I recently had pull data from a contact mgt. app's database where the
normalization brought up a question in my mind. The phone information
was
normalized as:

tblPhone:
fldPhoneID (PK)
fldPhoneTypeID (FK)
fldAreaCodeID (FK)
fldPhoneNumber
fldPhoneExtension

tblPhoneType:
fldPhoneTypeID (PK)
fldPhoneType

tblAreaCode:
fldAreaCodeID (PK)
fldAreaCode

To be consistent with this level of normalization wouldn't it make sense
to
have the first three numbers separated from the back 4 numbers? The
first
three would be very repetitious also. So the table would appear as:

tblPhone:
fldPhoneID (PK)
fldPhoneTypeID (FK)
fldAreaCodeID (FK)
fldPhoneNumberPrefixID  (FK)
fldPhoneNumber
fldPhoneExtension

tblPhoneType:
fldPhoneTypeID (PK)
fldPhoneType

tblAreaCode:
fldAreaCodeID (PK)
fldAreaCode

tblPhonePrefix:
fldPhoneNumberPrefixID  (PK)
fldPhoneNumberPrefix


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
The information contained in this transmission is intended only for the person or entity to which it is addressed and may contain II-VI Proprietary and/or II-VI Business Sensitive material. If you are not the intended recipient, please contact the sender immediately and destroy the material in its entirety, whether electronic or hard copy. You are notified that any review, retransmission, copying, disclosure, dissemination, or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited.