Better to store phone in three fields or as one?

Question

I am struggling with the decision to separate phone numbers stored in a MySQL database.

One school of thought is to break out the phone as:

• area code (123)
• prefix (123)
• suffix (1234)

Another is to simply place the file in a single field with whatever formatting deemed appropriate:

• 123456789
• (123) 123-4567
• 123-456-7890

My initial reason for thinking the first would be better is in terms of being able to quickly and easily gather statistical data based on the phone numbers collected from our members (X number of members have a 123 area code for example).

Is there really a 'right' way to do it? I do realize that paired with PHP I can retrieve and reformat any way I want but I'd like to use best practice.

Thanks for your advice

EDIT

I will only be storing North American phone numbers for the time being

Answer 1

I vote for one field, processing the data as you put it in so that it's in a known format. I've tried both ways, and the one-field approach seems to generate less code overall.

Answer 2

You want to store it in the most efficient way in the DB, precisely because it's so easy to reformat in PHP. Go for the all-numeric field, with no separators (1231231234) since that would be the best way. If you have international phone numbers, add the country code as well. Then in your code you can format it using regular expressions to look however you want it.