Common MySQL fields and their appropriate data types
Solution 1:
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.
Solution 2:
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
Solution 3:
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.