Designing a MySQL Database: Tips and Techniques
Most developers do not have a design background regarding MySQL, so when they create their database, it's inefficient, buggy and unreliable.
By properly setting up the correct specifications for your MySQL database, your web application will be efficient and reliable, since it meets industry standards for properly setting up a database.
You gain the following benefits from properly designing a MySQL database:
This tutorial will focus on designing a MySQL database for PHPweb applications and using the phpMyAdmin interface. Below are the requirements for you to design your database (starting from scratch):
Please note that this tutorial has been tested under MySQL version 5.0.67. Different MySQL versions might affect the design of your database, but basically the concept will be the same. This tutorial also assumes that the table format to be used will be MyISAM, which is the default table for MySQL.
Designing a MySQL Database: Tips and Techniques - Naming the Database, Tables, Field names and Data Types
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:
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 PHPscripts. 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.
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:
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.
Designing a MySQL Database: Tips and Techniques - Inputting the specifications into MySQL using phpMyAdmin
We will now input these specifications into the MySQLenvironment using the phpMyAdmin graphical interface. Follow these short steps:
Step 1: Log in to phpMyAdmin using your MySQL username and password.
Step 2: Click the "database" link, and in the "Create new database" section enter customercomplaint.
Step 3: On the "Create new table on database customercomplaint," section, enter "customertable" and in the number of fields, enter 6. Click "go."
Use the design specifications to configure the variables in the database. See the screen shot below for a complete guide:
Do not forget to check "Current_Timestamp" under the Default column in receivingdate field. "Unsigned" means it will accept only positive values, which can be set under "Attributes". "Zero Fill" means it will insert zeroes for spaces if the number of digits entered is less than five.
For example, if the product serial key is 456, MySQL will store it as "00456."
The detailed MySQL query for creating the designed table is as follows:
CREATE TABLE `customercomplaint`.`customertable` (
`customername` VARCHAR( 64 ) NOT NULL ,`price` DECIMAL( 7, 3 ) UNSIGNED NOT NULL ,`datepurchased` DATE NOT NULL ,`productkey` SMALLINT( 5 ) UNSIGNED ZEROFILL NOT NULL ,`complaintdetails` TEXT NOT NULL ,`receivingdate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE = MYISAM
After everything is set, click "Save." You can now start inserting data into your newly-designed database. You will notice it will only accept data according to your specifications and display errors for those are not within the design.