Common MySQL fields and their appropriate data types

Question

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:

1. You don't need to do any kind of arithmetic with it, and
2. 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.

Here are some common datatypes I use (I am not much of a pro though):

| Column           | Data type     | Note
| ---------------- | ------------- | -------------------------------------
| id               | INTEGER       | AUTO_INCREMENT, UNSIGNED                                                          |  
| uuid             | CHAR(36)      | or CHAR(16) binary                                                                |  
| title            | VARCHAR(255)  |                                                                                   |  
| full name        | VARCHAR(70)   |                                                                                   |  
| gender           | TINYINT       | UNSIGNED                                                                          |  
| description      | TINYTEXT      | often may not be enough, use TEXT 
                                     instead          
| post body        | TEXT          |                                                                                   |  
| email            | VARCHAR(255)  |                                                                                   |  
| url              | VARCHAR(2083) | MySQL version < 5.0.3 - use TEXT                                                  |  
| salt             | CHAR(x)       | randomly generated string, usually of 
                                     fixed length (x)    
| digest (md5)     | CHAR(32)      |                                                                                   |  
| phone number     | VARCHAR(20)   |                                                                                   |  
| US zip code      | CHAR(5)       | Use CHAR(10) if you store extended 
                                     codes      
| US/Canada p.code | CHAR(6)       |                                                                                   |  
| file path        | VARCHAR(255)  |                                                                                   |  
| 5-star rating    | DECIMAL(3,2)  | UNSIGNED                                                                          |  
| price            | DECIMAL(10,2) | UNSIGNED                                                                          |  
| date (creation)  | DATE/DATETIME | usually displayed as initial date of 
                                     a post                                       |  
| date (tracking)  | TIMESTAMP     | can be used for tracking changes in a 
                                     post                                        |  
| tags, categories | TINYTEXT      | comma separated values *                                                          |  
| status           | TINYINT(1)    | 1 – published, 0 – unpublished, … You 
                                     can also use ENUM for human-readable 
                                     values
| json data        | JSON          | or LONGTEXT       

In my experience, first name/last name fields should be at least 48 characters -- there are names from some countries such as Malaysia or India that are very long in their full form.

Phone numbers and postcodes you should always treat as text, not numbers. The normal reason given is that there are postcodes that begin with 0, and in some countries, phone numbers can also begin with 0. But the real reason is that they aren't numbers -- they're identifiers that happen to be made up of numerical digits (and that's ignoring countries like Canada that have letters in their postcodes). So store them in a text field.

In MySQL you can use VARCHAR fields for this type of information. Whilst it sounds lazy, it means you don't have to be too concerned about the right minimum size.

Since you're going to be dealing with data of a variable length (names, email addresses), then you'd be wanting to use VARCHAR. The amount of space taken up by a VARCHAR field is [field length] + 1 bytes, up to max length 255, so I wouldn't worry too much about trying to find a perfect size. Take a look at what you'd imagine might be the longest length might be, then double it and set that as your VARCHAR limit. That said...:

I generally set email fields to be VARCHAR(100) - i haven't come up with a problem from that yet. Names I set to VARCHAR(50).

As the others have said, phone numbers and zip/postal codes are not actually numeric values, they're strings containing the digits 0-9 (and sometimes more!), and therefore you should treat them as a string. VARCHAR(20) should be well sufficient.

Note that if you were to store phone numbers as integers, many systems will assume that a number starting with 0 is an octal (base 8) number! Therefore, the perfectly valid phone number "0731602412" would get put into your database as the decimal number "124192010"!!

I am doing about the same thing, and here's what I did.

I used separate tables for name, address, email, and numbers, each with a NameID column that is a foreign key on everything except the Name table, on which it is the primary clustered key. I used MainName and FirstName instead of LastName and FirstName to allow for business entries as well as personal entries, but you may not have a need for that.

The NameID column gets to be a smallint in all the tables because I'm fairly certain I won't make more than 32000 entries. Almost everything else is varchar(n) ranging from 20 to 200, depending on what you wanna store (Birthdays, comments, emails, really long names). That is really dependent on what kind of stuff you're storing.

The Numbers table is where I deviate from that. I set it up to have five columns labeled NameID, Phone#, CountryCode, Extension, and PhoneType. I already discussed NameID. Phone# is varchar(12) with a check constraint looking something like this: CHECK (Phone# like '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'). This ensures that only what I want makes it into the database and the data stays very consistent. The extension and country codes I called nullable smallints, but those could be varchar if you wanted to. PhoneType is varchar(20) and is not nullable.

Hope this helps!