Can we use Varchar for columns that will save only numbers [closed]

Question

I am doing a "user registration project" and I am newbie in database design.

I studied a lot of tutorials, but I didn't get a solution for this question.

Why there is not one particular datatype for all, that should support all these int, float, string, date , instead of using "int" for telephone , "char" for name and "varchar" for email.

Also is there any problem if I use "varchar" even though I am sure that "field" will store only numbers?

If we use correct data types, then performance/advantage is better? If I use varchar for columns that store only numbers, then performance will decrease, is this right?

Instead of struggling in future (like in MySQL convert column datatype from VARCHAR to INT on Stack Overflow) by using the wrong data types, it's better to use proper one before only?

Answer 1

There is no one catchall datatype because storage, working memory and CPU are not infinite resources. Nowadays you should almost always store data in the type that you will perform the most actions on it. Otherwise you will expend a lot of effort converting the data into different types before operating.

Your example of telephone numbers is a good one. Telephone numbers are probably best stored as strings. They are typically of fixed length and you will never want to perform numerical operations on them (addition, subtraction etc). You're more likely to want to append an international dialing code or something to the start of it, which would be a complex operation on a number, but trivial on a string.

This is also very relevant to indexing: If you store phone numbers as numbers, and start selecting on a string basis, your indexes will never be used. Let's say you want to find all phone numbers from Ireland:

select * from phone_numbers where varchar(number) like '353%';

If you put an index on this column in MySQL, it will always be a numerical index, which will be skipped for searches like this.*

Finally, storing phone numbers as numbers would violate the E.164 standard. The plus '+' character should technically be at the start of every number.

*Oracle allows functional indexes, and later version of MySQL and MariaDB allow you to create persistent virtual columns which can be indexed by a different datatype.