-1

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?

closed as off-topic by Andriy M, mustaccio, dezso, Philᵀᴹ, datagod Oct 11 '16 at 14:02

If this question can be reworded to fit the rules in the help center, please edit the question.

  • Why would it make sense? Data types save you the headache when someone happens to add a number like '13 quintillion and a bit more'. – dezso Oct 7 '16 at 20:53
  • 2
    I'm voting to close this question as off-topic because it does not make a lot of sense. – dezso Oct 7 '16 at 20:53
5

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.

  • Might also worth to mention storage efficiency. Storing 241 as tinyint unsigned would consume 1 byte while storing it as a varchar would require 4 bytes in best case assuming latin1 character set. And it can be up to 13 bytes on utf8mb4 for example. – Károly Nagy Oct 7 '16 at 15:54
  • @KárolyNagy - The string "241" will be the same length for utf8mb4 as for latin1. – Rick James Oct 7 '16 at 18:00

Not the answer you're looking for? Browse other questions tagged or ask your own question.