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

Why VARCHAR and not CHAR?

VARCHAR has a variable length according to the record contained in it.

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

In the VARCHAR column only 5 characters of 20 will be used, 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 being CHAR (20) and weighing more in the bank.

Never use numeric type to save data such as phone / cell

Suppose you want to save the number "0800 800 8000" in the bank, what will happen?

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

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

A pattern I usually use is the one listed below. It can be seen on my site .

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

For values that contain numbers and miscellaneous characters, care needs to be taken, especially if the pattern changes a lot. Attending fixed phones and cell phones, such as those in São Paulo that has 9 digits, it is possible to validate at the time of entering the system and a good mascara can return to the original format, but the thing will be complicated if your system has to support also international numbers and regional numbers in Brazil. In these cases, if you do not want to bother, use VARCHAR same, but in my examples below I in some applications converted national phones to BIGINT and I had no problems.

More standard numeric values, such as ZIP code and CPF, certainly recommend storing as numeric. Mark in the colum as 'ZEROFILL' that it will place zeros to fill in the initial values.

• Business name VARCHAR (255)
• Fancy Name VARCHAR (255)
• CNPJ BIGINT (14), VARCHAR (18)
• Foundation Date DATE
• Email VARCHAR (100)
• Website VARCHAR (100)
• Phone BIGINT (14) (You already consider 9-digit numbers). When in doubt, use VARCHAR
• Cell BIGINT (14) (Already considered 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 INT (8), VARCHAR (10)
• Description TEXT

Note that with the exception of UF, I always use VARCHAR, but I 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 you have, it is some very unknown and with some 60.

Snippets to format numeric result of certain fields (PHP)

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

I'm stuck here because anyone who is asking this type of question will definitely end up looking for this 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);
    }

}

If you are not going to do the numbers (add, multiply, etc.), you do not have much to use numeric types.

Use CHAR or VARCHAR , as appropriate.