Database Design

Most Web Developers do not have a design background regarding MySQL. So when they create their database, it’s inefficient, buggy and unreliable.

For the meantime; I am still using the MySQL 5.5 and never moved to the 6.0 version. I will be writing a second page about the new version when I start using it instead of 5.5.

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.

WAMP is the software I use for testing PHP scripts and accessing phpMyAdmin. If you’re not familiar with it or XAMMP then try to download anyone of them and test it or go to www.mysql.com and get the full information there.

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.

To help you easily understand the processes, we will make a real life example. let’s create a new database as if you wanna start a new 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: Tedzy).

Price (Example: 10.25). 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: 2013-03-17, 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: 00123, 84625, 00023.

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 the database name and the tables name too (which is one table for now).

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 “customer_complaints,” and then we can name the database table “customer_table.” The more you can simplify the naming, the more it will be convenient to use it in the associated PHP/ASP or any other language scripts. Avoid using difficult or mixed-case database names, tables and field names because it can create confusion when you formulate the PHP/ASP or other queries to the database.

For the corresponding data types for each of those variables, you can check the mysql websites to see all the data types found.

Leave a Reply