I decided to make a MySQL Data Type Cheat Sheet to make it easier for me, and my students, to find appropriate data types for our MySQL databases.
Step One – MySQL Text Columns
FIRST – Look at your text (string) data, Ask yourself if the data column will be indexed? That means will us use it to search with? You may be searching for things like “user_name”, “password”, “id”, cell_phone_number, and date_of_birth. If it is text and you plan to search it, use a VARCHAR data type. If it is text and you do not plan to search (index) it, use a TEXT data type.
Step Two – Find a MySQL Column Data Type
Second – Now just look on this simple table and find a field (column name) that is pretty close to the data you are planning to store in your database. Chances are that one of these 22 will be all you need. Or you will see how easy it is to modify it!
| Column | Data type | Comment |
|---|---|---|
| date (creation) | BIGINT(20) | Best for manipulation in PHP (unix timestamp) where NOT Indexed in MySQL (I use this the most!) |
| date (creation) | DATE / DATETIME | Best for MySQL Indexing where time is needed. Usually displayed as initial date of a post. |
| date (tracking) | TIMESTAMP | May be used for tracking changes in a post. I prefer doing it in PHP with BIGINT. |
| date_of_birth | DATE | Best for date of birth/death where no time is needed. Also date_ joined, or date_started/stopped. |
| description | TINYTEXT | NO INDEX! May not be enough, use TEXT instead. |
| VARCHAR(255) | ||
| file_name | VARCHAR(255) | Path should be encoded in a php script, not taking up room in a database. |
| five_star_rating | DECIMAL(3,2) | UNSIGNED Five-Star Rating or Karma |
| full_name | VARCHAR(70) | Or given-name, middle_name, and family_name. |
| gender | TINYINT(1) / CHAR(1) | CHAR: M=Male, F=Female. TINYINT: UNSIGNED 1=Transgender Female Pre-OP |
| id | BIGINT/INTEGER | AUTO_INCREMENT, UNSIGNED, PRIMARY |
| password | VARCHAR(255) / CHAR(32) | Best use bcrypt! Digest: md5=CHAR(32) adjust fixed length for sha1, sha256, bcrypt etc. |
| phone_number | BIGINT(20) | All Numbers, start with country code only. Only 8 bytes! |
| phone_number | VARCHAR(20) | |
| post_body | TEXT | NO INDEX! |
| price | DECIMAL(10,2) | UNSIGNED May need more in the Philippines or Japan. |
| salt_or_code | CHAR(x) | Randomly generated string, usually of fixed length (x) |
| status | TINYINT(1) / CHAR(1) | 1 or P = published, 2 or U = unpublished. |
| tags, categories | TINYTEXT | NO INDEX! Comma separated values * |
| title | VARCHAR(255) | |
| url | TEXT | NO INDEX! MySQL version > 5.0.3: can use VARCHAR(2083) w/INDEX |
| zip_code | CHAR(4) (5), (6), (10) | Philippines CHAR(4), USA CHAR(5), Canada: CHAR(6) All others (USA extended codes): CHAR(10) |
Step Three – MySQL Field Names
The MySQL Field (Column) Name is important. It MUST be descriptive of what is in that field. For example, I have seen fields named “date”, “text”, and “sex”. In one class I had, half my students built a database structure, and the other half of the students would make statements to fill in the data, based solely on the field name only. One student had a field that was “sex”. Confused as to what information the student was asking for, the other student populated the field with a simple “Yes!” Wonder what she was thinking? The student wanted an answer like “Male” or “Female”. A better field name in this case would have been “gender”.
For field names / column names, use one to three words, in lowercase, separated with underscores.
“date_of_birth” and “membership_date” are much clearer field names, than the student who just wrote “date” so he would have less to type, and more time to spend dating! The other student simply answered “No!” because she was too busy studying, and she graduated with honors! (Note: He did end up with more time for dating. He failed a number of courses and was there for another year of dating!)
“creation_date” and “last_update” would both be preferred to “date7″ and date8” in being understood.
“Text” goes without saying as one of the worst field names in history. During my class discussion about the above field name “sex”, some of the students were taking a reproductive health class at the same time, and were discussing being sexually active. Therefore, “sexually_active” and “sexual_preference” became suggestions for how to improve the “sex” field name.
The point here is that databases stay around for a long, long time. Even outliving people. I have decades old databases! So choose your field and column names very carefully.
You have about 32 characters for each one, use 2-3 whole words, in lowercase, and tie the words together with underscores. You will be glad you did.
P.S. I am currently working on a database that has the fields, “bio”, “time”, “confirmed”, and “generated_string”. These are all fails because the programmer failed to convey to me the actual meaning in these field names.