Storing in database

[propaganistas]

Storing in database

[Propaganistas]

@epalmans It all depends on your app's requirements. Is your application operation for one country or multiple? How fine-grained should searching phone numbers be? Do users expect to see their inputted value after saving, or are they fine with an after-save formatted value?

In the meantime I've taken the following approach that sort of keeps middle-ground in most scenarios. It requires some boilerplating though:

---

• 5 database columns:
  • phone: holds the user input as-is
  • phone_country: holds the correlated country
  • normalized_phone: identical to phone with everything but numerics stripped
  • normalized_phone_national: the formatted national phone number (using formatNational() and whitespaces stripped)
  • normalized_phone_e164: the E164 format of the phone number (using formatE164())

---

All normalized columns can be set in a saving() observer on the model.

Note: normalized_phone can also be achieved by a virtual column and a REGEX function if your DB supports it (MySQL 8.0 for example).

---

Input forms interact with the phone and phone_country attributes. This way users are presented with their very own inputted phone number

Searches in phone numbers operate on normalized_phone, normalized_phone_national and normalized_phone_e164 alltogether using OR clauses. The search term is stripped first accordingly:

$query->where('normalized_phone', 'LIKE', preg_replace('/[^0-9]/', '', $term).'%')
    ->orWhere('normalized_phone_national', 'LIKE', preg_replace('/[^0-9]/', '', $term).'%')
    ->orWhere('normalized_phone_e164', 'LIKE', preg_replace('/[^+0-9]/', '', $term).'%');

To increase the hit rate you could potentially include some additional clauses using a parsed term (using phone()->formatXXXX()) with a sensible country default.

---

Searching through a list of international phone numbers in a user friendly way is unfortunately quite cumbersome.
I'm not saying this is the best approach and there is surely room for optimization. It just covers a lot.

[Propaganistas]

E.164 is a format to globally and uniquely identify a phone number across the world. So yes, reverse formatting will always be possible to one of the provided formats. It’s of course not possible to convert back to the way a user has once entered the value.

[Propaganistas]

You could use the following columns and workflow:

// Note the length of both columns; there's no need to accommodate for more.
// 36 is rather arbitrary; should be fine I think
// But there's definitely no need for the default 255.
$table->string('phone_number', 36)->nullable();
$table->string('phone_country_code', 2)->nullable();

$phone = phone($number);

• If a phone number is validated:
  • Store the number in E.164 format: $phone->formatE164()
  • Store the country code: $phone->getCountry()
  • Store the extension, if present, separately
  • If you expect to store phone numbers with extensions you might want to consider storing extensions separately
• If a phone number is NOT validated:
  • Store the number as-is
  • Don't store a country code
• Searching: perform a two-fold search:
  • Search for the string as-is (to capture non-validated phone numbers)
  • Parse the string to E.164 format first and then search
  • So something like this:

    $database->where('phone_number', $string)
             ->orWhere(function($query) use ($string) {
                 $parsed = phone($string, $country); // Supply country code here if applicable.
    
                 $query->where('phone_number', $parsed->formatE164())
                       ->where('phone_country_code', $parsed->getCountry());
              });

[epalmans]

@Propaganistas Can I pick your brain on this....? On a greenfield project, would you personally do it like this? To me, storing phonenumber in just a single column in E.164-format seems easier. Better even.... ? not sure

[epalmans]

Hi @Propaganistas . Many thanks for your elaborate answer!
And very good arguments indeed. Although, not fully applicable to my specific use-case: I don't care that much that users are seeing their own handcrafted formatting (sorry users 😄) and rather have the benefit of having to just manage one field.
That said, will your package always be able to correctly - and unambiguously - convert back from each E.164 formatted number? Or could one number have multiple possible outcomes (for example, due to overlapping country codes) ?

[epalmans]

thanks again @Propaganistas ! Great! 👍