How to assign data types to different column types
June12
This article describes the appropriate datatypes for specific column types eg clientID=smallint(3) and clientName=varChar(40)
While developing a dynamic website, a database plays the key role. So we developers should pay particular attention to our tables (MYSQL/SQL…), as a database has a huge effect on the overall performance of a website. So while designing a MySQL/SQL or what ever database/table keep in mind the datatypes size, speed++
Below is an example of an accounting database describing the appropriate datatypes each column should have. You can take this table as a reference while designing your other tables.
Accounting Database |
|
| Column N a m e | Column Data Type |
| Invoice Number | SMALLINT(4) UNSIGNED |
| Client ID | SMALLINT(3) UNSIGNED |
| Invoice Date | DATE |
| Invoice Amount | DECIMAL(10,2) UNSIGNED |
| Invoice Description | TINYTEXT |
| Client ID | SMALLINT(3) UNSIGNED |
| Client Name | VARCHAR(40) |
| Client Street Address | VARCHAR(80) |
| Client City | VARCHAR(30) |
| Client State | CHAR(2) |
| Client Zip | MEDIUMINT(5) UNSIGNED |
| Client Phone | VARCHAR(14) |
| Contact Name | VARCHAR(40) |
| Contact Email Address | VARCHAR(60) |
| Expense ID | SMALLINT(4) UNSIGNED |
| Expense Category ID | TINYINT(3) UNSIGNED |
| Expense Amount | DECIMAL(10,2) UNSIGNED |
| Expense Description | TINYTEXT |
| Expense Date | DATE |
| Expense Category ID | TINYINT(3) UNSIGNED |
| Expense Category | VARCHAR(30) |
To choose your data types:
- Identify whether a column should be a text, number, or date type.This is normally an easy and obvious step. You will find that numbers such as ZIP codes and dollar amounts should be text fields if you include their corresponding punctuation (dollar signs, commas, and hyphens), but you’ll get better results if you store them as numbers and address the formatting elsewhere.
- Choose the most appropriate subtype for each column.For improved performance, keep in mind two considerations:
- Fixed-length fields (such as CHAR) are generally faster than variable-length fields (such as VARCHAR), but they also take up more disk space.
- The size of any field should be restricted to the smallest possible value, based upon what the largest possible input could be. For example, if the largest a number such as Client ID could be is in the hundreds, set the column as an unsigned three-digit SMALLINT (allowing for up to 999 values).
- You should keep in mind that if you insert a string five characters long into a CHAR(2) field, the final three characters will be truncated. This is true for any field in which the length is set (CHAR, VARCHAR, INT, etc.).
Source: http://www.peachpit.com/articles/article.asp?p=30885&seqNum=7
