Phone Numbers Table Design

Question

Phone Numbers Table Design

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

maechismo_8514 SSCoach Points: 17510 More actions · Answer 1

Thanks for that,

But as per my scenario, the contact numbers sometimes could be interntational and sometimes they are not, purely depending upon the customer(Politician). We would have to store whatever numbers they would like us to contact them on.

Now, they could give us their numbers in USA and also in other countries as well. I would have to add Extension for USA and Why is this table design not internationalised?. Like for example, if a user wants to retrieve the PhoneNumber , they will be presented with the full number including Country code, city code etc...on the application the developers going to design using the right queries.........Basically these tables are going to become a ground work for the contact management they are going to design......

Please suggest:)

Cheers!!!!

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Heh... science it out... how many parts of a phone number for a NANPA phone number and how many parts for an International Number?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

Jeff Moden (1/17/2009)
Heh... science it out... how many parts of a phone number for a NANPA phone number and how many parts for an International Number?

After that, ask yourself... "If I want to put both in the same table, what columns do I need and does it really matter what I call the columns in the table?"

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

maechismo_8514 SSCoach Points: 17510 More actions · Answer 4

Yes, I know what you mean now Jeff. It would rather be cumbersome and will also make the application slower.

So, I have to create separate table for NANPA phone numbers alone and their codes? I will leave this table design as it is just now because I am designing it for a UK website. I will wait and see what type of contact numbers we would be receiving and according to the need I will later fabricate the table. do you think that should be ok?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

Krishna_DBA (1/17/2009)
So, I have to create separate table for NANPA phone numbers alone and their codes?

No, no... look at what I wrote in my last post. 1 Table...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

maechismo_8514 SSCoach Points: 17510 More actions · Answer 6

CREATE TABLE ContactPhone
(
--Numbers for NANPA--
CountryCode_NPAnvarchar(10),--Country code for NANPA DEFAULT '1'
AreaCode_NPA nvarchar(10),--Area code for NANPA
Exchange_NXX nvarchar(10),--Exchange code for NANPA
Number_XXXXnvarchar(10),--Last 4 digits for NANPA
--Numbers for Non-NANPA
CountryCodenvarchar(10),
AreaCode nvarchar(10),
PhoneNumbernvarchar(10)
)

Like this? and I will have to provide detailed description about NANPA for developers?........

Sorry for bugging you all the time..but you had been a great help. I am learning new things......:)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

Krishna_DBA (1/17/2009)
CREATE TABLE ContactPhone
(
--Numbers for NANPA--
CountryCode_NPAnvarchar(10),--Country code for NANPA DEFAULT '1'
AreaCode_NPA nvarchar(10),--Area code for NANPA
Exchange_NXX nvarchar(10),--Exchange code for NANPA
Number_XXXXnvarchar(10),--Last 4 digits for NANPA
--Numbers for Non-NANPA
CountryCodenvarchar(10),
AreaCode nvarchar(10),
PhoneNumbernvarchar(10)
)
Like this? and I will have to provide detailed description about NANPA for developers?........
Sorry for bugging you all the time..but you had been a great help. I am learning new things......:)

Nope... did you see anywhere that international numbers have an NPA? Stop being specific... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

maechismo_8514 SSCoach Points: 17510 More actions · Answer 8

Jeff Moden (1/18/2009)
Krishna_DBA (1/17/2009)
CREATE TABLE ContactPhone
(
--Numbers for NANPA--
AreaCode_NPA nvarchar(10),--Area code for NANPA
Exchange_NXX nvarchar(10),--Exchange code for NANPA
Number_XXXXnvarchar(10),--Last 4 digits for NANPA
-- For International numbers
CountryCodenvarchar(10),
AreaCode nvarchar(10),
PhoneNumbernvarchar(10)
)
Like this? and I will have to provide detailed description about NANPA for developers?........
Sorry for bugging you all the time..but you had been a great help. I am learning new things......:)
Nope... did you see anywhere that international numbers have an NPA? Stop being specific... 😉

oops modified the table without the Country code for NANPA......

Anam Verma SSCertifiable Points: 5365 More actions · Answer 9

Krishna

I would say, try to generalise your table strucutre as much you can. i know its not possible to standardise the phone number formats from all the countries, but atleast you can segment the country codes, area codes and phone numbers as mentioned in earlier posts.

The advantage of this would be, you can re-use your this table structure anywhere else. Another advantage is that, lets say you come into a scenario where you have to clean the unwanted data or lets say wrong telephone numbers in your table. You can't perform this task unless your data is standardise.

After looking at the NANPA (sorry if i spelled wrong) codes, today i came to know how telephone numbers are stored in US. I am in Australia and telephone numbers (land line, mobile or any fax) are all 10 digits including area code which is of 2 digits, therefore, leaving only 8 digit telephone number. It is very easy if i have area code in seperate column and telephone number in seperate column. then i can easily say that someone has put a invalid phone number in the column if its length is not equal to 8.

My view point is, step back, look forward and then implement the best solution.

Anam Verma SSCertifiable Points: 5365 More actions · Answer 10

Krishna_DBA (1/17/2009)

CREATE TABLE ContactPhone
(
--Numbers for NANPA--
AreaCode_NPA nvarchar(10),--Area code for NANPA
Exchange_NXX nvarchar(10),--Exchange code for NANPA
Number_XXXXnvarchar(10),--Last 4 digits for NANPA
-- For International numbers
CountryCodenvarchar(10),
AreaCode nvarchar(10),
PhoneNumbernvarchar(10)
)

Any specific reason for using nvarchar instead of varchar. I dont think you are going to insert any national characters in these columns.

maechismo_8514 SSCoach Points: 17510 More actions · Answer 11

anam (1/18/2009)

Krishna_DBA (1/17/2009)
CREATE TABLE ContactPhone
(
--Numbers for NANPA--
AreaCode_NPA nvarchar(10),--Area code for NANPA
Exchange_NXX nvarchar(10),--Exchange code for NANPA
Number_XXXXnvarchar(10),--Last 4 digits for NANPA
-- For International numbers
CountryCodenvarchar(10),
AreaCode nvarchar(10),
PhoneNumbernvarchar(10)
)
Any specific reason for using nvarchar instead of varchar. I dont think you are going to insert any national characters in these columns.

We are not sure about what type of data we are going to expect. Thats true that I am not going to store any national characters, I have chosen this in case.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

I keep trying to walk people into a more generic solution but I keep seeing the word "NPA" as a column name. This is something like what I'm trying to lead folks into...

CountryCode VARCHAR(3), -- Covers all country codes...

Prefix1 VARCHAR(4), -- Covers NPA, Mexican zone, and City codes

Prefix2 VARCHAR(4), -- Covers NXX and other subdivisions if available

Number VARCHAR(15), -- Anything that's left

Extension VARCHAR(10) -- May want to fine tune length, but I've seen up to 6 digits

Separate table would identify the part names and rules for each country code.

Not sure if it will ever happen, but the NANPA numbers could change to 11 or 12 digits in the future. The above table layout would handle that change.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)