normalization question

This question is inspired from my last question. It seems to me that for a
db to be properly normalized, should an area code be seperate from a phone
number? Is this incorrect?
--
SQL2K SP3
TIA, ChrisRProbably not. The phone number really is (in North America) 10 digits, of
which the first three are the area code. Beyond N America, things change
somewhat (I believe).
That said, you can have a table of phone numbers where there is an area code
column and a number column, with a FK from the area code to a table of area
codes. This ensures that only known area codes are put into your phone
number table.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"ChrisR" <bla@.noemail.com> wrote in message
news:erXMq1M0EHA.2572@.tk2msftngp13.phx.gbl...
This question is inspired from my last question. It seems to me that for a
db to be properly normalized, should an area code be seperate from a phone
number? Is this incorrect?
--
SQL2K SP3
TIA, ChrisR|||Actually, one could make an argument that both area code and prefix should
be separated out as keys, as they can be used to uniquely identify regions.
We use a database for that purpose, in order to determine qualification for
DSL. However, if you're only using them for display, they should definitely
be in the same column, IMO...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OxaITBN0EHA.2788@.TK2MSFTNGP15.phx.gbl...
> Probably not. The phone number really is (in North America) 10 digits, of
> which the first three are the area code. Beyond N America, things change
> somewhat (I believe).
> That said, you can have a table of phone numbers where there is an area
code
> column and a number column, with a FK from the area code to a table of
area
> codes. This ensures that only known area codes are put into your phone
> number table.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>|||I'd be careful about prefix. Where I work right now, I use 9+1+area
code+number. In another place, it was 4+area code+number. In yet another,
it was 8+area code+number. Of course, at home, it's 1+area code+number.
Sometimes, internationalization can be a right PITA...
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:urZFEGN0EHA.1332@.TK2MSFTNGP10.phx.gbl...
Actually, one could make an argument that both area code and prefix should
be separated out as keys, as they can be used to uniquely identify regions.
We use a database for that purpose, in order to determine qualification for
DSL. However, if you're only using them for display, they should definitely
be in the same column, IMO...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OxaITBN0EHA.2788@.TK2MSFTNGP15.phx.gbl...
> Probably not. The phone number really is (in North America) 10 digits, of
> which the first three are the area code. Beyond N America, things change
> somewhat (I believe).
> That said, you can have a table of phone numbers where there is an area
code
> column and a number column, with a FK from the area code to a table of
area
> codes. This ensures that only known area codes are put into your phone
> number table.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>|||>However, if you're only using them for display, they should definitely
> be in the same column, IMO...
Are you referring to the (ac and number), or the (ac and prefix) in this
statement?
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:urZFEGN0EHA.1332@.TK2MSFTNGP10.phx.gbl...
> Actually, one could make an argument that both area code and prefix should
> be separated out as keys, as they can be used to uniquely identify
regions.
> We use a database for that purpose, in order to determine qualification
for
> DSL. However, if you're only using them for display, they should
definitely
> be in the same column, IMO...
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OxaITBN0EHA.2788@.TK2MSFTNGP15.phx.gbl...
> > Probably not. The phone number really is (in North America) 10 digits,
of
> > which the first three are the area code. Beyond N America, things
change
> > somewhat (I believe).
> >
> > That said, you can have a table of phone numbers where there is an area
> code
> > column and a number column, with a FK from the area code to a table of
> area
> > codes. This ensures that only known area codes are put into your phone
> > number table.
> >
> > --
> > Tom
> >
> > ---
> > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> > SQL Server MVP
> > Columnist, SQL Server Professional
> > Toronto, ON Canada
> > www.pinnaclepublishing.com
> >
>|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ejuU7JN0EHA.3452@.TK2MSFTNGP14.phx.gbl...
> I'd be careful about prefix. Where I work right now, I use 9+1+area
> code+number. In another place, it was 4+area code+number. In yet
another,
> it was 8+area code+number. Of course, at home, it's 1+area code+number.
> Sometimes, internationalization can be a right PITA...
>
I think we're talking about different things in regards to prefix. I'm
talking about:
(XXX) YYY - ZZZZ
Where XXX is the area code and YYY is the prefix. For
internationalization it's:
+CC - (AreaCode) - (Prefix) - (Something)
(CC = Country Code)
AFAIK, there is always some sort of areacode and prefix, although the
number of digits are variable, but the something can be totally different
depending on country -- there may be more levels of hierarchy, e.g. I think
in some Asian countries they have 4 or 5.
The local phone system you're on will determine the prefix you're
talking about, 9 + 1, 4 + , etc.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||"ChrisR" <bla@.noemail.com> wrote in message
news:e8Do%23JN0EHA.1296@.TK2MSFTNGP10.phx.gbl...
> >However, if you're only using them for display, they should definitely
> > be in the same column, IMO...
> Are you referring to the (ac and number), or the (ac and prefix) in this
> statement?
AC and number. I would store it (if it were for display only) as:
XXXYYYZZZZ, or maybe XXX-YYY-ZZZZ, or however you might want to display
it on the client.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Ah. Where we live, what you call "prefix" we call "exchange". So, I guess
in order to validate things, there would only be certain exchanges within an
area code.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Bqf1TN0EHA.1524@.TK2MSFTNGP09.phx.gbl...
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ejuU7JN0EHA.3452@.TK2MSFTNGP14.phx.gbl...
> I'd be careful about prefix. Where I work right now, I use 9+1+area
> code+number. In another place, it was 4+area code+number. In yet
another,
> it was 8+area code+number. Of course, at home, it's 1+area code+number.
> Sometimes, internationalization can be a right PITA...
>
I think we're talking about different things in regards to prefix. I'm
talking about:
(XXX) YYY - ZZZZ
Where XXX is the area code and YYY is the prefix. For
internationalization it's:
+CC - (AreaCode) - (Prefix) - (Something)
(CC = Country Code)
AFAIK, there is always some sort of areacode and prefix, although the
number of digits are variable, but the something can be totally different
depending on country -- there may be more levels of hierarchy, e.g. I think
in some Asian countries they have 4 or 5.
The local phone system you're on will determine the prefix you're
talking about, 9 + 1, 4 + , etc.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Just as an FYI:
> For
> internationalization it's:
> +CC - (AreaCode) - (Prefix) - (Something)
> (CC = Country Code)
> AFAIK, there is always some sort of areacode and prefix
In Sweden, we have no prefix. We have CountryCode (obviously), AreaCode and "something". :-)
Are code can be from two to four numbers. "Something" can be from five to 8 or nine numbers (not
sure how many it can go to).
As one point, Sweden was bragging, I believe it was in Newsweek, that they have the longest
telephone numbers in the world. In a country with some 8.5 (at the time) mill people. I found that
partly amusing, and partly really really worrying...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23Bqf1TN0EHA.1524@.TK2MSFTNGP09.phx.gbl...
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:ejuU7JN0EHA.3452@.TK2MSFTNGP14.phx.gbl...
> > I'd be careful about prefix. Where I work right now, I use 9+1+area
> > code+number. In another place, it was 4+area code+number. In yet
> another,
> > it was 8+area code+number. Of course, at home, it's 1+area code+number.
> >
> > Sometimes, internationalization can be a right PITA...
> >
> I think we're talking about different things in regards to prefix. I'm
> talking about:
> (XXX) YYY - ZZZZ
> Where XXX is the area code and YYY is the prefix. For
> internationalization it's:
> +CC - (AreaCode) - (Prefix) - (Something)
> (CC = Country Code)
> AFAIK, there is always some sort of areacode and prefix, although the
> number of digits are variable, but the something can be totally different
> depending on country -- there may be more levels of hierarchy, e.g. I think
> in some Asian countries they have 4 or 5.
> The local phone system you're on will determine the prefix you're
> talking about, 9 + 1, 4 + , etc.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>|||And for further info, we don't have any prefix or area code in Denmark. We
used to have a 2 digit area code in the past, but for many years we've just
had an 8 digit phonenumber.
In the past I worked on deploying a CRM system to our offices worldwide.
That was sometimes quite a challenge to get the phone- and fax numbers
entered in the right way into the system due to all the differencies in
phone number syntax around the world.
Regards
Steen
Tibor Karaszi wrote:
> Just as an FYI:
>> For
>> internationalization it's:
>> +CC - (AreaCode) - (Prefix) - (Something)
>> (CC = Country Code)
>> AFAIK, there is always some sort of areacode and prefix
> In Sweden, we have no prefix. We have CountryCode (obviously),
> AreaCode and "something". :-)
> Are code can be from two to four numbers. "Something" can be from
> five to 8 or nine numbers (not sure how many it can go to).
> As one point, Sweden was bragging, I believe it was in Newsweek, that
> they have the longest telephone numbers in the world. In a country
> with some 8.5 (at the time) mill people. I found that partly amusing,
> and partly really really worrying...
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in
> message news:%23Bqf1TN0EHA.1524@.TK2MSFTNGP09.phx.gbl...
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:ejuU7JN0EHA.3452@.TK2MSFTNGP14.phx.gbl...
>> I'd be careful about prefix. Where I work right now, I use 9+1+area
>> code+number. In another place, it was 4+area code+number. In yet
>> another,
>> it was 8+area code+number. Of course, at home, it's 1+area
>> code+number.
>> Sometimes, internationalization can be a right PITA...
>>
>> I think we're talking about different things in regards to
>> prefix. I'm talking about:
>> (XXX) YYY - ZZZZ
>> Where XXX is the area code and YYY is the prefix. For
>> internationalization it's:
>> +CC - (AreaCode) - (Prefix) - (Something)
>> (CC = Country Code)
>> AFAIK, there is always some sort of areacode and prefix,
>> although the number of digits are variable, but the something can be
>> totally different depending on country -- there may be more levels
>> of hierarchy, e.g. I think in some Asian countries they have 4 or 5.
>> The local phone system you're on will determine the prefix you're
>> talking about, 9 + 1, 4 + , etc.
>>
>> --
>> Adam Machanic
>> SQL Server MVP
>> http://www.sqljunkies.com/weblog/amachanic
>> --|||"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:elA9%239V0EHA.3408@.tk2msftngp13.phx.gbl...
> And for further info, we don't have any prefix or area code in Denmark. We
> used to have a 2 digit area code in the past, but for many years we've
just
> had an 8 digit phonenumber.
> >
> > In Sweden, we have no prefix. We have CountryCode (obviously),
> > AreaCode and "something". :-)
Thanks for the info guys!
Both are countries I haven't worked with yet. I had a bear of a time w/
Asia (mostly due to lack of quality data, but it's been a few years since
that project so maybe things have improved) -- I can't wait until I get to
cover Europe :-)
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--