SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2005
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question phone number handling

    Hello!



    I'm wondering if anyone has any input on how to handle telephone number entry!



    Here's what I have so far:

    1. The numbers will be in US format (i.e. 3 digit area code, 3 digits, 4 digits)

    2. I'll have three text boxes to enter the characters in above line item separately

    3. I was thinking the datatype should be smallint, since tiny int might be too small, and int is not needed to that extent for each of the three values

    4. The whole 10 digit value for the phone number will be stored in one column, and there will be a separate column for the extension, which I'm thinking that could be a char(10) or should it just be an int, and all characters will be numbers.



    I'm thinking the phone number should be of a datatype of numbers only....but I'm looking for any comments.



    Thanks!

  2. #2
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,629
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Unless you need the ability to query based on area code or exchange or need to power some automated dialing, I would just give the users a single textbox and store the numbers in a single text field.

  3. #3
    SitePoint Wizard
    Join Date
    Feb 2007
    Posts
    1,274
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Because a datatype is all digits it does not follow that it should be stored in a numeric field. Rarely do you need to multiply phone numbers ;-)

    Just store as a text field / tring type. You may optionally use a regex to validate the format and (possibly) normalize it.

  4. #4
    SitePoint Enthusiast
    Join Date
    Aug 2005
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for the reply!

    I made the datatype nvarchar(), and just put a required field validator and did a range validator of type integer.

  5. #5
    SitePoint Member xela220's Avatar
    Join Date
    Oct 2005
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I agree. The only thing that I might add is that I have seen code around that allows you to create a mask for the text field so that it looks like (xxx)xxx-xxxx which helps the user from entering invalid characters.

  6. #6
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,629
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Insofar as invalid characters go, I will go back to the question my original post: will you be doing anything mechanical with this data? If not, then why constrain people to a pre-ordained pattern?

  7. #7
    SitePoint Member xela220's Avatar
    Join Date
    Oct 2005
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I see what you are saying.

    What I meant to say is that you can use varchar or interger, but I would be more concerned with consistancy. Think of the queries that you might need to run. Suppose that you need to find all numbers in the 212 area code. You will need to do a select picking the firts three number (where left(PhoneNumer,3) = 212). If you have inconsistent data like "(212)" and "212," then you will need to do further work on the query and possibly further maintenance in the future as other characters might creep in (periods, spaces, happy faces, whatever).

    You can use integer and just place all the 10 number. Now when you select the phone number and display it in a form, you will have to parse it and format it. If it is already in the format that you want (varchar) then you can select it straight out. Also integers go up to like a gazillion, so you can potentially get a number larger than 10 characters. With varchar the character length is restricted, and you can even place a constrain, so the data has to be in the proper format (xxx)xxx-xxxx. In case you have people doing updates in tables using SQL studio straight in.

    Either way works! The main thing is to prevent the user from just entering whatever they want and having inconsistent records in the database. I mentioned the masked text box because it helps the user to enter the right format. I personally prefer varchar and so do the other guys, apparently.


    Hope this helps out.

  8. #8
    SitePoint Author silver trophybronze trophy
    wwb_99's Avatar
    Join Date
    May 2003
    Location
    Washington, DC
    Posts
    10,629
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    What I meant to say is that you can use varchar or interger, but I would be more concerned with consistancy. Think of the queries that you might need to run. Suppose that you need to find all numbers in the 212 area code. You will need to do a select picking the firts three number (where left(PhoneNumer,3) = 212). If you have inconsistent data like "(212)" and "212," then you will need to do further work on the query and possibly further maintenance in the future as other characters might creep in (periods, spaces, happy faces, whatever).
    That would qualify as "doing something mechanical with the data." Though I would argue such cases are rare. If you want people in manhattan, you will probably search by zip, not phone number. If querying by area code was a requirement, it should probably be in a separate field.

    And what sort of production application has people updating directly from management studio?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •