What are the appropriate data types for columns like address, email, phone and mobile phone for SQL database?

Question

What kind of data are recommended to create the following fields: address, email, mobile number/phone?

Answer 1

For the address, email and phone/cell phone fields I recommend using VARCHAR because it has a variable size and correctly save the data.

Why VARCHAR and not CHAR?

SWEEP has a variable size according to the record contained in it.

Example: I will insert the word "Yuran" that contains 5 characters in one VARCHAR(20) column and the same record in another CHAR(20)

In the VARCHAR column will be used only 5 characters of the 20, the size of that record will be equal to that of a CHAR(5).

In the CHAR column all 20 characters will be used regardless of the size of the record with CHAR(20) and weighing more on the bench.

 

Never use numeric type to save data like phone/cell phone

Let’s assume you want to save the number "0800 800 8000" in the bank, which will happen?

• Depending on the numeric type will exceed the limit size and you will not be able to save the data in the database.

• You will lose all "0" to the left of the number and with it you lose data integrity.

Answer 2

One pattern I usually use is listed below. It can be seen on my website.

In some cases I have listed more than one example. The types and sizes are for Mysql/Mariadb.

In the case of values that contain numbers and several characters, it is necessary to be careful, especially if the pattern changes a lot. To answer fixed and mobile phones, as those of São Paulo that has 9 digits, it is possible to validate when entering the system and a good mask can return to the original format, but it will get complicated if your system has to support also international numbers and regional numbers of Brazil. In these cases, if you do not want to bother, use VARCHAR even, but in my examples below I in some applications converted national phones to BIGINT and had no problems.

More standard numerical values, such as ZIP code and CPF, would certainly recommend storing as numerical. Mark the column as 'ZEROFILL' and it will set zeros to fill in the initial values.

• Social Reason VARCHAR(255)
• Fancy name VARCHAR(255)
• CNPJ BIGINT(14), VARCHAR(18)
• Date of Foundation DATE
• Email VARCHAR(100)
• Website VARCHAR(100)
• Telephone BIGINT(14) (Already consider 9 digit numbers). In doubt, use VARCHAR
• Cellular BIGINT(14) (Already consider 9 digit numbers) In doubt, use VARCHAR
• Responsible VARCHAR(255), VARCHAR(100)
• Address VARCHAR (150)
• Address, number VARCHAR(20) (remember: "numbers" may contain other characters)
• Neighborhood VARCHAR(50)
• City VARCHAR(50)
• UF CHAR(2), TINYINT(2) (use of numbers if related to another table)
• ZIP CODE INT(8), VARCHAR(10)
• Description TEXT

Note that with the exception of UF, I always use VARCHAR, but limit the maximum to a reasonable value to be found. For example, there is no city or neighborhood in Brazil with more than 50 characters, or if it has, it is some unknown and with some 60.

Snippets to format numerical result of certain fields (PHP)

The formatting of the phone and mobile phone is more complicated because it does not provide international numbers or numbers 0800 the 0300. The rest have been tested and work well.

Colo here because whoever is asking this kind of question will surely end up looking for it later.

/**
 * QSES component helper.
 *
 * @package  Alligo.Joomla
 * @since    1.6
 */
class AlgFilter {

    /**
     * Remove valores não numericos (inclusive , e .)
     *
     * @param   string  $input  Valor de entrada
     * @return  int
     */
    public static function getNumbers($input)
    {
        return preg_replace("/[^0-9]/", "", $input);
    }

    /**
     * Formata 90050123 em 90.050-123
     *
     * @param   int    $numero  Numero a ser formatado
     * @return  string
     */
    public static function formatoCep($input)
    {
        return preg_replace('/^(\d{2})(\d{3})(\d{3})$/', '\\1.\\2-\\3', $input);
    }

    /**
     * Formata 90050123 em 90.050-123
     *
     * @param   int    $numero  Numero a ser formatado
     * @return  string
     */
    public static function formatoCpf($input)
    {
        return preg_replace('/^(\d{1,3})(\d{3})(\d{3})(\d{2})$/', '${1}.${2}.${3}-${4}', $input);
    }

    /**
     * Formata CNPJ
     *
     * @param   int    $numero  Numero a ser formatado
     * @return  string
     */
    public static function formatoCnpj($input)
    {
        return preg_replace("/^(\d{2})(\d{3})(\d{3})(\d{4})(\d{2})$/", "\\1.\\2.\\3/\\4-\\5", $input);
    }

    /**
     * Formata 1234567890 em (12) 3456-7890
     *
     * @param   int    $numero  Numero a ser formatado
     * @return  string
     */
    public static function formatoTelefone($numero)
    {
        return preg_replace('/(\d{2})(\d{4})(\d*)/', '($1) $2-$3', $numero);
    }

}

Answer 3

If you are not going to do math with the numbers (add, multiply, etc), there is not much to use numerical types.

Use CHAR or VARCHAR, as appropriate.