January 15, 2009 at 8:02 pm
Hello All,
I am looking for different design ideas in this scenario:
I have got a Contacts Table for customers. Now these customers are going to be high Profile customers. Definitely, I am expecting large number of Telephone numbers and we predict each customer would be having at least 6 Personal Telephone numbers including mobile numbers, Fax numbers(Home Fax, business Fax), Mobile numbers+Their Work numbers at different places. So, we are in a situation where we have to create seperate tables for Phone numbers.
This is going to be a One-to-Many Relationship between the contacts main table and the Phone numbers table.
I could create tables:
CREATE TABLE Contacts
(
ContactID int not null primary key identity(1,1)
ContactFirstName nvarchar(30),
...
...
..
.
)
CREATE TABLE PhoneNumbers
(
PhoneNumberID int not null primary key identity(1,1)
PersonalPhonenumber navrchar(20),
BusinessPhonenumber nvarchar(20),
WorkPhonenumber1 nvarchar(20),
WorkPhonenumber2 nvarchar(20),
.
.
.
.
MobilePhonenumber nvarchar(20),
HomeFaxNumber nvrchar(20),
WorkFaxNumber nvarchar(20),
ContactID int References Contacts(ContactID)
)
The table just goes on storing the different numbers for this customers at home, work may be their abroad contact numbers.
I am looking for better ideas to see whether we can create seperate table saying Phone Cetegory and then link it to the Phone numbers table? I really need some guidance and a little push into it if you guys could any??
Thanks a lot!!!!
January 15, 2009 at 11:20 pm
I think the below table design should solve your problem.
You need to create following tables:
PhoneType --Storee all the categories/types of phone numbers
(ID, Name)
PhoneNumber --Stores all the phone numbers
(ID,TypeID, Number)
ContactPhoneNumber --Stores the relationship between Contact and phone numbers
(ContactID, PhoneNumberID)
--this can also be done without the ContactPhoneNumber. The phone number table will look like below in this scenario:
PhoneNumber --Stores all the phone numbers
(ID,TypeID, Number, ContactId)
-Vikas Bindra
January 15, 2009 at 11:26 pm
That's a much better design.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 15, 2009 at 11:50 pm
Thanks Jeff!! 🙂
-Vikas Bindra
January 16, 2009 at 12:06 am
i think below table design could be use.
Contact(ContactID, ContactFirstName, ContactLastName, ....)
PhoneType(PhoneTypeID, PhoneTypeDesc, ....)
ContactPhone(ContactID, PhoneTypeID, PhoneNumber, Active/Inactive)
Contact - Store all Contacts information
PhoneType - Store PhoneTypes E.g PersonalPhonenumber,
BusinessPhonenumber, WorkPhonenumber1,
WorkPhonenumber2.....
ContactPhone - Store the Contact-PhoneType association.
hope this table design will serve your purpose.
Abhijit - http://abhijitmore.wordpress.com
January 16, 2009 at 7:01 am
Vikas and Abhijit have offered better solutions. Your original proposal is inflexible as you limit the # of phone numbers to the number of columns in the table. With the offered solutions you can have 0 to N phone numbers for each person, and repeat phone numbers are stored once. This solution is better normalized.
You also need to determine how you will store international numbers as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 16, 2009 at 7:35 am
Jack Corbett (1/16/2009)
Vikas and Abhijit have offered better solutions. Your original proposal is inflexible as you limit the # of phone numbers to the number of columns in the table. With the offered solutions you can have 0 to N phone numbers for each person, and repeat phone numbers are stored once. This solution is better normalized.You also need to determine how you will store international numbers as well.
Sorry for my previous short response with no explanation. Jack is correct about the suggestions of Vikas and Abhijit. He also brings up the question on the storeage of internation numbers and that's a hot spot in the world of telephony almost as bad as people storing just open text full names instead of the component parts of names....
I wouldn't store a whole phone number... I'd do it properly and store it as component parts made up of Country Code, Area Code (NPA), Exchange (NXX), Number (Last 4 digits) for NANPA "North American Numbering Plan Association" numbers in the U.S and Canada and Country Code, City Code, Number for what Americans refer to as "International" Numbers. Not to be a ring knocker, but I've written two worldwide call accounting packages in T-SQL and have worked with several others I've found that splitting and storing the numbers as their component parts will save you just oodles of nasty headaches down the road... just like splitting full names will do the same.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 16, 2009 at 7:45 am
Interesting, Jeff. I am in process of developing a system that needs to handle international phone numbers and we decided to go with this design:
Phone_Numbers(country_code, phone_number)
One question, did you do any format validation?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 16, 2009 at 12:20 pm
Hi guys,
First let me thank you for your kind replies. I really appreciate that.
Let me get you the right scenario. I am involved in designing database storage for Politicians Contact details from 4 different countries who are part of customers table which also includes many Organitsations, local authorities, media etc...
Now as you see they could be having large number of different phone numbers.
this could go too complicated just for PhoneNumbers from 3 different countries? Isn't it? I am thinking to create a new Table as
PrimaryContacts
(PrimaryPhoneNumber,PrimaryAddress,PrimaryemailID)---But even the above design could go complicated as they could be having primary HouseNumber,workNumber,MobileNumber,FaxNumber etc...
Any Idea guys?
My criteria is allow to store as many phone numbers as we could so that we can contact them when needed.
Contact - Store all Contacts information
PhoneType - Store PhoneTypes E.g PersonalPhonenumber,
BusinessPhonenumber, WorkPhonenumber1,
WorkPhonenumber2.....
ContactPhone - Store the Contact-PhoneType association.
According to Abhijits design, I would have to create 6 unique ID's If a particular customer has 6 work phone numbers. is that right??
And Jeff has raised a good point, I was actually coming ther step by step. I would have to store International extensions too as they could be having contact numbers in different countries. I think thats very good idea for me, thanks Jeff.
January 16, 2009 at 12:56 pm
Jack Corbett (1/16/2009)
Interesting, Jeff. I am in process of developing a system that needs to handle international phone numbers and we decided to go with this design:Countries (country_code (iso), name, international_dialing_code, phone_country_code) - international_dialing_code is for dialing out of the country and phone_country_code is for dialing into the country.
Phone_Numbers(country_code, phone_number)
One question, did you do any format validation?
No... didn't store any format characters in the data, if that's what you mean. What we did do, though, was we bought a table of International City codes and (for the 2nd one I did) a rather large set of tables that basically contains all of the calling rules for the world. None of that "formatted" the numbers in a way that people were, ummm, used to seeing in their local area. It presented the phone numbers simply with spaces between the various parts.
We did, however, validate numbers insofar as being real using that data. That's a bit complex, if you think about it, but we did end up building a high speed method that worked very, very well and fast enough so you could actually use if for routing calls, if it came to that.
We bought the data from a company called CCMI and they're really good folks. A lot of the data wasn't needed and a lot of it needed to be normalized, but they're one of the leading "authorities" (if you will) on all the telephony stuff you could possible imagine. The other company is called "Telcordia" and they have a product called the LERG. Same information as CCMI... just split up a lot differently.
The interesting part is that they not only have Lat/Lon for each wiring center/central office, they also have a thing called "VnH" coordinates. Basically, it's a set of vertical 'n' horizontal coordinates that flatten out the Earth using a Donald projection of the Earth and that makes it so distance calculations can be done simply using the Pythagorean Theorum instead of all that Cos stuff you'd normally have to use. Adding the "EAA Modifier" to a concatenated copy of the VnH coordinates creates another "column" called the "EAA" or "Exchange Area Address"... and, if those are the same, the call is guaranteed to be local (well, unless you're using "FlyByNight, Inc" as your telephone provider). Needless to say, VoIP has a slightly different set of rules. 😛
Another good source but less intense source of information is NANPA (North American Plan Numbering Association, they are the keepers of ALL phone numbers in the U.S., Canada, and many territories)... they've got all the NPA/NXX numbers and some other central office information available for free. Here's the URL for that...
http://www.nanpa.com/reports/reports_cocodes_assign.html
Look for "All States" to get the full list of utilized NPA/NXX's for North America and their territories like GUAM, USVI, Puerto Rico, and the maritime numbers.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 16, 2009 at 1:00 pm
By the way... if you do contact CCMI, the wizard behind the curtain that makes it all work is called Ed Sullivan. Laugh if you want, that's his real name. Back in 2001-2003 when I was working for a particular company who shall rename nameless, Ed and I worked a fair bit together on a remote basis. Because of all the checks I had in my code, I could sometimes help him out a bit when one of the LECs or CLECs screwed things up for data. Tell him "Jeff Moden says "Hi!"". I doubt that he'll remember me, but that's OK, too.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 16, 2009 at 1:03 pm
Krishna_DBA (1/16/2009)
According to Abhijits design, I would have to create 6 unique ID's If a particular customer has 6 work phone numbers. is that right??
That's correct... and you shouldn't have a column called "PrimaryPhoneNumber". You should have a PhoneNumber column and a PhoneNumberType column. "Mind the pennies... the dollars will take care of themselves."
And Jeff has raised a good point, I was actually coming ther step by step. I would have to store International extensions too as they could be having contact numbers in different countries. I think thats very good idea for me, thanks Jeff.
You're very welcome. Thank you for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 16, 2009 at 2:43 pm
Jeff Moden (1/16/2009)
By the way... if you do contact CCMI, the wizard behind the curtain that makes it all work is called Ed Sullivan. Laugh if you want, that's his real name. Back in 2001-2003 when I was working for a particular company who shall rename nameless, Ed and I worked a fair bit together on a remote basis. Because of all the checks I had in my code, I could sometimes help him out a bit when one of the LECs or CLECs screwed things up for data. Tell him "Jeff Moden says "Hi!"". I doubt that he'll remember me, but that's OK, too.
Thanks for the info Jeff. I know I looked at several web sites, I can't remember which one I used. It's bookmarked on my work PC, but I'm not at it, and several countries have a couple of formats. Boy it would be easier if we had something standard. Even as little standardization as an email address.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 17, 2009 at 1:50 pm
Hello People,
I have creatd something like this:
PhoneType Table:
CREATE TABLE PhoneType
(
PhoneTypeIDintNOT NULLPRIMARY KEYIDENTITY(1,1),
PhoneDescriptionnvarchar(20)
)
ContactPhone Table:
CREATE TABLE ContactPhone
(
CountryCodenvarchar(10),
AreaCodenvarchar(10),
Exchangenvarchar(10),
PhoneNumbernvarchar(10)
)
ALTER TABLE ContactPhone ADD ContactID int References Contacts(ContactID)
ALTER TABLE ContactPhone ADD PhoneTypeID int References PhoneType(PhoneTypeID)
Do you think this should be fine as of now? Later according to the requirements I will update is as needed......
Cheers!
January 17, 2009 at 3:00 pm
Not if you want to include anything outside of the U.S.A, Canada, or either of their NANPA territories.
The country code for [font="Arial Black"]any [/font]telephone number that has an NPA/NXX (Area Code/Exchange) is "1". That's it, pure and simple and, yes, that includes Canada.
Mexico uses a Country code of "52" (if memory serves correctly), zones (there used to be 9... don't know how many they have now), and a number.
Most other countries use a 1 to 3 digit Country code, a 0 to 4 digit city code, and a number.
Your layout is fine for the U.S.A and Canada except that you need to add a column for "Extension"... even then, it's just not going to work for anything "international".
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply