CODEWITHSUNDEEP
sqldatabasepostgresqltypesphone-number
SharkLaser
SharkLaser

Reputation: 773

Proper Storage of Millions of Phone Numbers in Postgres

I'm working on a project that is focused on meta information about phone numbers.

Using postgres, we will be seeding the database with millions of phone numbers and I'm concerned about the best way to store this information.

Right now I've been considering a phones table with each row representing the phone number as a string. Then simply joining off of that... Something like below: 

+-----------------------+             +-----------------------+
|     phone_numbers     |             |     phones            |
+-----------------------+             +-----------------------+
| id: integer           +-------+     | id: integer           |
| digits: string        |       |     |                       |
|                       |       +-----+ phone_number: integer |
|                       |             |                       |
|                       |             |                       |
|                       |             |                       |
+-----------------------+             +-----------------------+

How should the database schema be designed with regard to the storage of phone numbers?

Upvotes: 5

Views: 3986

Answers (1)

David Aldridge
David Aldridge

Reputation: 52376

The only pattern you can really follow for international phone numbers is to break them into three parts:

  1. International direct dialling prefix (maybe with associated table to lookup the country code: http://en.wikipedia.org/wiki/List_of_country_calling_codes)
  2. The phone number itself.
  3. Extension number(s)

Stored them as strings in order to preserve any desired formatting spaces or leading zeros, and validate that they follow an approved pattern.

Make a choice on whether to store national phone number "0" prefixes or not -- best to stick to the E.123 presentation of numbers where possible: http://en.wikipedia.org/wiki/E.123

Upvotes: 8

Related Questions