Хранение номеров телефонов в БД

Хранение номеров телефонов в базе данных MySQL не такая уж тривиальная задача, как может показаться на первый взгляд. Один из первых вопросов при проектировании БД: какой тип ячейки БД использовать? Цифровое поле, например INT в MySQL не подходит для этих целей, так как номера некоторых телефонов (например мобильные номера в Украине) могут начинаться с нуля, а следовательно, если попытаться сохранить такой номер как число - ведущий ноль (нули) будут потеряны. Для хранения номеров телефонов лучше всего использовать текстовый тип столбца: CHAR.

Чтобы ускорить поиск по текстовым данным имеющим различную длину, в отдельном индексируемом цифровом столбце (TINYINT) можно хранить длину номера телефона и использовать её в условиях поиска - отсекать лишние номера при поиске. Но такая реализация базы данных нужна только при очень больших объемах телефонных номеров и в большинстве случаев её можно не использовать.

Если планируется хранение номеров телефонов в месте с кодом города и страны - лучше всего разделить эти данные на 4 отдельных столбца: код1, код2, номер телефона, полный номер телефона (код1, код2, номер).

Код1 - это, в основном, код страны (+20 - Египет, +380 - Украина и т.д.), но некоторые первичные коды телефонов могут и не иметь привязки к стране, например: 388 - European Telephony Numbering Space (Europe-wide services); 870 - Inmarsat "SNAC" service и т.д. Поэтому называть такую колонку country_code или подобным образом логически не правильно. Помимо всего прочего один первичный код может принадлежать нескольким странам: +1 - Америка, Канада и другие; 7 - Россия, Казахстан и т.д. Подробнее о таких кодах можно прочитать тут: Географические коды телефонных номеров. Хранить данные о первичном коде телефонного номера можно и в цифровом формате, так как все такие коды не могут начинаться с нуля, но исходя из соображения, что стандарт в будущем может поменяться - безопаснее сразу хранить такую информацию в текстовом виде. Максимальное количество символов Код1 - пять.

Код2 может быть кодом города, мобильного оператора или чего-нибудь еще, соответственно mob_code, city_code не являются логичными названиями для таких данных. Следует отметить, что в некоторых странах такие коды, возможно, могут начинаться и с нуля, поэтому выбор типа данных такого столбца - очевиден. Максимальное количество символов в таком столбце, скорее всего, не будет более пяти.

Номер телефона - об этом столбце написано в начале статьи. Кол-во символов в таком номере, обычно не превышает 7 штук.

Полный номер телефона содержит в себе информацию столбцов БД MySQL Код1, Код2 и Номер телефона. Этот столбец существенно упростит проверку на дублирующиеся номера. Тип и размер данных в нем высчитывается на основании колонок Код1, Код2 и Номер телефона.

Если планируется привязка нескольких номеров телефонов к одной организации, в таблице базы данных следует предусмотреть колонку с идентификатором фирмы (firm_id) и колонку порядковый номер телефона (number). Это решение очень поможет в составлении легковесных SQL запросов, когда нужно выводить список многих организаций с одним (например: основным) номером телефона.

Вывести только 1 номер телефона для всех фирм из БД

SELECT f.firm, p.phone
FROM table_firm AS f
LEFT JOIN table_phone AS p ON (f.id = p.firm_id AND p.number = 1)

Колонка порядковый номер должна содержать последовательные номера, начинающиеся с единицы, без разрывов: 1,2,3.

Хранение номеров телефонов, зачастую, подразумевает и сопутствующую их привязку к персональной информации - ФИО, название организации и т.д., а для этого, в свою очередь во многих странах требуются специальные разрешения, регистрации баз данных и т.п.

Хранение номеров телефонов в виде хеша

Если номер телефона нужен только для подтверждения регистрации пользователя - как средство защиты от ботов и мультиаккаунтов можно использовать следующую схему. Номер хранится в открытом виде только до подтверждения регистрации. После подтверждения создается и записывается в БД взломоустойчивый хеш номера телефона, а сам номер удаляется:

Пример относительно взломоустойчивого хеша на PHP

$hash = md5(md5($phone_number).'saltBLF@#$%^');

При необходимости восстановления пароля пользователя через мобильный телефон, отослав на него код активации, нужно запросить через форму у пользователя его номер телефона, вычислить хеш, сравнить с тем что в БД и выслать на него инструкции по активации. RFL_HABR22032013

Полезная информация

Телефонный план нумерации (Wikipedia)

Опубликовано: 2013/03/20

HTML-код ссылки на эту страницу:

<a href="https://petrenco.com/mysql.php?txt=168" target="_blank">Хранение номеров телефонов в БД</a>

29815

Комментарии

Мне вот интересно, при условии хранения в базе только хеша телефонного номера и получения от пользователя только, собственно, этого номера - откуда Вы собрались взять $id_phone_number для вычисления хеша и поиска его в базе?

Тут логично использовать $user_id, его можно брать по логину, указанному в той же форме, что и номер телефона.

Да, действительно, $id_phone_number брать будет неоткуда, исправлено, спасибо!
По поводу $user_id - это не обязательно, да и ктому же, если заставить пользователя вводить еще и логин, тогда уж можно будет использовать для создания соли хеша $id_phone_number, если предусмотрено, что у каждого пользователя только 1 номер телефона.

13 января 2015 г. в 16:18
Ivan

Добавить комментарий