Home arrow MySQL arrow Page 2 - Designing a MySQL Database: Tips and Techniques

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

You are planning to develop a PHP web application that needs a MySQL database. Now what? You need to design your MySQL database first. Need a little help? Keep reading.

  1. Designing a MySQL Database: Tips and Techniques
  2. Naming the Database, Tables, Field names and Data Types
  3. Finalization of Database Design Specifications
  4. Inputting the specifications into MySQL using phpMyAdmin
By: Codex-M
Rating: starstarstarstarstar / 19
July 13, 2009

print this article



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.

>>> More MySQL Articles          >>> More By Codex-M

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: