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:

• Speed - Because the database is efficient and uses the correct data types and character length, query speed will improve. 
  
  
• Security - Security improves because you allow specific data to be stored in the MySQL database, and nothing else.  
  
  
• Storage space efficiency - Since you are developing a database that is purely based on data specifications, there will be no unnecessary bytes or characters stored in the MySQL database. This saves disk space.

This tutorial will focus on designing a MySQL database for PHP web applications and using the phpMyAdmin interface. Below are the requirements for you to design your database (starting from scratch):

• XAMPP, for testing PHP scripts and accessing phpMyAdmin. If you're not familiar with it, read this XAMPP article for more information. 
  
  
• A List of fields and their data types. You'll need a complete understanding of the data to be processed and stored.  
  
  
• The number of characters in every piece of data to be stored.

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.