What is the best way to store phone numbers in a database to avoid duplication?

What is the best way to store phone numbers in a database to avoid duplication?

BigDataCloud January 7, 2020

Share

How often have you opened your customer database and spent hours cleaning and formatting phone numbers? It is the most common problem hampering the efficiency of your business. Yet, many companies still ignore them and end up collecting invalid or duplicate data. 

At the heart of this problem, lies a simple question - should you store a phone number as an integer or as a string in a database. But, the Internet forums are divided on this topic leading to confusion. Most of these arguments are one dimensional and ignores the real challenges behind storing phone numbers. 

Just like dates, a phone number is not just a sequence of random numbers. It is divided into various parts which carry information about the regions associated with the number and are designed to be globally unique. Therefore, it is highly recommended that the phone numbers are stored along with their county code to avoid data ambiguity. This will prevent data duplication and standardise your formatting.  

E.164 is an international format which defines a general format for international telephone numbers. Based on this formating, the number contains a country code (CC), a national destination code (NDC), and a subscriber number (SN). There can be up to 15 digits in an E.164 number. 

Hence, storing a phone number is more than deciding what data types of use on a database.

Key challenges in storing a phone number:

  • Choosing the right data type. (Integer or String)
  • Choosing the format type. (International or National)
  • Identifying the type of phone number. (mobile or landline)
  • Verifying the phone number.

Best way to store Phone Numbers in a database.

Here are the steps which you need to take before you store a phone number in a database.

  • Take input from the user.
  • Identify the country of the user. (You can either use the IP address of the user to identify this or ask for country-input )
  • Check the formatting of the phone number and compare with that country’s format
  • If Invalid, prompt the user to re-enter
  • If Valid, convert the format into E.164 in string and store in your DB.

Hence, you need to employ various processes before you store them. You can either build these processes by yourself or use various APIs available. BigDataCloud has a FREE API which you can use to solve all your phone verification challenges.

Utilising BigDataCloud’s Phone Verification API

BigDataCloud has 2 sets of APIs which can help you automatically validate and convert the phone number into various formats. 

Option 1: Validate phone number using IP location

Option 2: Validate phone number using country code

You can check our free phone validation API docuimentation. The API is free to use with a limit of 1,000 queries per month.

Once you verify the phone number using BigDataCloud’s Phone Verification API, you will receive a JSON output with more details like Line Type, Currency, Flag icon and so on. You can use these to enhance your customer info.

{
    "isValid": true,
    "e164Format": "+14156665555",
    "internationalFormat": "+1 415-666-5555",
    "nationalFormat": "(415) 666-5555",
    "location": "San Francisco, CA",
    "lineType": "FIXED_LINE_OR_MOBILE",
    "country": {
        "isoAlpha2": "US",
        "isoAlpha3": "USA",
        "m49Code": 840,
        "name": "United States of America",
        "isoName": "United States of America (the)",
        "isoAdminLanguages": [
            {
                "isoAlpha3": "eng",
                "isoAlpha2": "en",
                "isoName": "English",
                "nativeName": "English"
            }
        ],
        "unRegion": "Americas/Northern America",
        "currency": {
            "numericCode": 840,
            "code": "USD",
            "name": "US Dollar",
            "minorUnits": 2
        },
        "wbRegion": {
            "id": "NAC",
            "iso2Code": "XU",
            "value": "North America"
        },
        "wbIncomeLevel": {
            "id": "HIC",
            "iso2Code": "XD",
            "value": "High income"
        },
        "callingCode": "1",
        "countryFlagEmoji": "🇺🇸"
    }
}
Share