Common MySQL fields and their appropriate data types
I am setting up a very small MySQL database that stores, first name, last name, email and phone number and am struggling to find the 'perfect' datatype for each field. I know there is no such thing as a perfect answer, but there must be some sort of common convention for commonly used fields such as these. For instance, I have determined that an unformatted US phone number is too big to be stored as an unsigned int, it must be at least a bigint.
Because I am sure other people would probably find this useful, I dont want to restrict my question to just the fields I mentioned above.
What datatypes are appropriate for common database fields? Fields like phone number, email and address?
Answer
Someone's going to post a much better answer than this, but just wanted to make the point that personally I would never store a phone number in any kind of integer field, mainly because:
- You don't need to do any kind of arithmetic with it, and
- Sooner or later someone's going to try to (do something like) put brackets around their area code.
In general though, I seem to almost exclusively use:
- INT(11) for anything that is either an ID or references another ID
- DATETIME for time stamps
- VARCHAR(255) for anything guaranteed to be under 255 characters (page titles, names, etc)
- TEXT for pretty much everything else.
Of course there are exceptions, but I find that covers most eventualities.