Thread: Best way to store/search phone numbers?

I am doing a standard company info database, with phone numbers, and I
was wondering if there was a general concensus about the best way to
store phone numbers.  My problem is multiple users will be entering in
data via a web browser (PHP backend), and the formatting may not be
the same in all cases.  I'm not sure if I should store some sort of
standard formatting in a plain text field (e.g., "(555) 555-5555") or
strip out all the punctuation characters.  There is also the option of
storing the parts in different columns, but that seems a bit overkill
(but would make searching possibly faster).

My 2 cents:  Concatenating data is easier than
extracting data from a larger element.

I'd use different fields (country code, area code,
phone number, extension, etc.) and instruct the users
not to use punctuation.  The data will (hopefully) be
a little cleaner; and easier to use.  Then, if you
want it all in one field, use an update query to
concatenate the values with formatting characters into
a separate field for easy reporting/printing.

Best of luck,

Andrew

--- Raise Exception <raiseexception@yahoo.com> wrote:
> I am doing a standard company info database, with
> phone numbers, and I
> was wondering if there was a general concensus about
> the best way to
> store phone numbers.  My problem is multiple users
> will be entering in
> data via a web browser (PHP backend), and the
> formatting may not be
> the same in all cases.  I'm not sure if I should
> store some sort of
> standard formatting in a plain text field (e.g.,
> "(555) 555-5555") or
> strip out all the punctuation characters.  There is
> also the option of
> storing the parts in different columns, but that
> seems a bit overkill
> (but would make searching possibly faster).
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the
> unregister command
>     (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)


__________________________________________________
Do You Yahoo!?
Make a great connection at Yahoo! Personals.
http://personals.yahoo.com

Consistently formatted data is easier to work with, so
I suggest storing the phone numbers without any
non-numeric characters.  This would require writing
some application code to extract the numeric
characters, either within the database using a
trigger, or within the client application.

Arguments about whether to store data as a single
string/number or whether to break it up could go
either way.  Probably you would want to at least have
a separate field for extension numbers, and if
international numbers are likely to be stored, for
country codes as well.

--- Andrew Gould <andrewgould@yahoo.com> wrote:
> My 2 cents:  Concatenating data is easier than
> extracting data from a larger element.
>
> I'd use different fields (country code, area code,
> phone number, extension, etc.) and instruct the
> users
> not to use punctuation.  The data will (hopefully)
> be
> a little cleaner; and easier to use.  Then, if you
> want it all in one field, use an update query to
> concatenate the values with formatting characters
> into
> a separate field for easy reporting/printing.
>
> Best of luck,
>
> Andrew
>
> --- Raise Exception <raiseexception@yahoo.com>
> wrote:
> > I am doing a standard company info database, with
> > phone numbers, and I
> > was wondering if there was a general concensus
> about
> > the best way to
> > store phone numbers.  My problem is multiple users
> > will be entering in
> > data via a web browser (PHP backend), and the
> > formatting may not be
> > the same in all cases.  I'm not sure if I should
> > store some sort of
> > standard formatting in a plain text field (e.g.,
> > "(555) 555-5555") or
> > strip out all the punctuation characters.  There
> is
> > also the option of
> > storing the parts in different columns, but that
> > seems a bit overkill
> > (but would make searching possibly faster).
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the
> > unregister command
> >     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>
>
> __________________________________________________
> Do You Yahoo!?
> Make a great connection at Yahoo! Personals.
> http://personals.yahoo.com
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org


__________________________________________________
Do You Yahoo!?
Find a job, post your resume.
http://careers.yahoo.com

Andrew Gould writes:

> I'd use different fields (country code, area code,
> phone number, extension, etc.) and instruct the users
> not to use punctuation.

This will break down once someone from a different area/country tries to
enter his phone number.

You think it won't happen?  I have tried to do business with quite a few
places that didn't think my business would happen.  (I didn't actually end
up happening, too.)

OTOH, I once did work for an ISP that had a very specific interest in the
breakdown of phone numbers by area code, exchange, and such.  I used a
text field with a couple of functional indexes, and it worked well for us.

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter