Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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?

like image 226
SharkLaser Avatar asked Mar 17 '23 17:03

SharkLaser


1 Answers

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

like image 157
David Aldridge Avatar answered Mar 19 '23 07:03

David Aldridge