Naming the Database, Tables, Field names and Data Types - MySQL

To help you easily understand the processes, we will present the tutorial with a real life implementation. Let us assume you are given a task to design a web application that will receive customer complaints in your website. During your brainstorming session, you came up with the following data that need to be gathered from the web form every time there is a customer-related complaint:

• Customer name (Example: John Doe).  
  
  
• Price (Example: 34878.456). This should not exceed 4 digits before the decimal point and allow only 3 digits after the decimal point. Also, the stored values should not be negative.  
  
  
• Date purchased (using this format: 2009-05-16, or yyyy-mm-dd).  
  
  
• Product Serial Key (integer only, should not be negative values, up to 5 digits, use zeros to fill in if less than 5 digits) Example: 00567, 56453, 00021.
  
  
• Complaint details (accept text input of any length).  
  
  
• Receiving date of complaint (displays the current time and date the form was submitted).

Now that we have clearly defined the data that needs to be gathered, the next step is to assign a name to the database and the table.

According to the MySQL guidelines, the database name, table name and field names should not exceed 64 characters in length, and ideally should not contain special characters, to avoid the use of a back tick.

For simplicity we can name the database "customercomplaint," and then we can name the database table "customertable." The more you can simplify the naming, the more it will be convenient to use it in the associated PHP scripts. Avoid using difficult or mixed-case database names, tables and field names because it can create confusion when you formulate the PHP queries to the database.

For the corresponding data types for each of those variables, below is a screen shot of the available data types for MySQL:

The most commonly used MySQL data types are VARCHAR, DECIMAL, DATE, INT, TEXT and TIMESTAMP.

VARCHAR is commonly used for variable length strings up to 255 characters. If you are storing and processing data that is alphanumeric in nature, this data type is the most suitable. Common examples of actual data that fits this type include people's names, zip codes, telephone numbers and any type of alphanumeric data not exceeding 255 characters in length. Do not use the VARCHAR type when storing numbers which will be used for computation; it might cause some computation-related problems. In other words, it might affect the accuracy and integrity of the computation.

DECIMAL is appropriate for storing numbers that will be used for computation. In MySQL, we can specify the number of allowable digits in numbers to be stored (including the number of decimal places). We can also specify whether negative values are allowed.

Assigning the proper length of the DECIMAL type can be tricky. For example, if you need to store only 5 digits before the decimal point and allow only 3 digits after the decimal point, the proper length/values to be configured in the database will be:

Decimal (5+3, 3) or Decimal (8, 3)

Examples of allowed numbers include: 12345.678, 56872.690, 11.6 and 12.568

The following numbers, however, will return an error: 128781.1, 8972865.231

The DATE data type is recommended for storing dates. The default date format for MySQL is 2009-05-18 [year-month-day].

The INT data type is recommended if you are storing numbers which will not contain a decimal point. INT stands for integer. Again, like DECIMAL, correctly specifying INT in MySQL could be tricky.

There are several integer types which you need to know, as well as the maximum number of digits they can have:

• TINYINT - This type will accept up to 3 digits as the maximum. 
  
  
• SMALLINT- Allows up to 5 digits maximum.
  
  
• MEDIUMINT - This type will accept up to 8 digits as the maximum. 
  
  
• INT - This type will let you go up to 10 digits.  
  
  
• BIGINT - If you plan to allow up to 20 digits, this is recommended.

TEXT is a very useful data type that will accept text inputs, a mixture of just any characters that comprise the content of any web form inquiry. VARCHAR can only accept up to 255 characters, but TEXT can be used to store data that exceeds that amount.

When the TIMESTAMP data type is selected, by checking "CURRENT_TIMESTAMP" as the default, MySQL automatically returns the actual date and time of every MySQL data insertion.