Basic MySQL Best Practices

Software developers nowadays usually work in various fields of programming and development. Such as UI design, server-side programming(even in multiple languages like PHP, Asp.NET, etc.), and database management. In each area, if we use and follow some traditional best practices. Then, they will always be organized and easy to keep track of from time to time among other developers. As a developer, we may not be highly expert in working with databases, but maintaining a few MySQL best practices will definitely help us deliver quality products.

Why follow Database best practice conventions?

Suppose you can design a database and its table that is easily understandable to other developers who are working on the same database(or will work in the future). In such cases, you will get a clap for what you have done after experiencing the efficiency, easily understandably you have kept for them, and you will be in a good eye of them too. To direct you for beginning in such way, in this tutorial, i will try to give some of the MySQL best practices. I will use the MySQL database in PHPMyAdmin as management here(as they are used by most of the open-source developers).

Deep Look At DB Engine/Collation Settings:

First, you must have to keep an eye open from the beginning when you are creating a database table for your application. You should be aware of these two things at least:

• Database Engine
• Collation

Although they are set by default to ‘MyIasm’ and ‘latin1_swedish_ci’ correspondingly, sometimes, for a robust application, they should be changed to a suitable type for optimized support from the database. For example, if your application strictly needs transaction operations and/or foreign key constraints, then you should use ‘Innodb’ as your database engine. It’s not required for all tables but only for the tables that make use of such operations. Similarly, ‘utf8_general_ci’ and ‘utf8-unicode_ci’ are two general, mainly used alternative forms for collation/character-set. So, check and make the necessary changes from your PhpMyadmin’s ‘operations’ tab for a particular table. You will get advantages like:

• Server coding will be efficient enough to keep track of transaction-type operations, whereas, in the case of the default ‘MyIasm’ database engine, you had to take care of these types of operations yourself right inside the coding, which might be buggy.
• If your application goes live(it contains some data) and you want to change the collation type/engine type at that time, you may get into some unexpected trouble. It’s better to take care of this at the beginning.

Database Table Column/Field Definitions:

Another point is when we are creating a table and filling in the field info. We are always in a hurry to only fill in the necessary parts like ‘auto-increment,’ ‘column name,’ ‘type,’ and ‘length’ and save the table. Most of us don’t even see the other info/fields. But they also need to be marked seriously. Like, at the bottom, you will be able to see a field for comment about the table, write necessary description here for the reasons this table is to be used. Also, there is a similar field for each column you are about to create to add some comments about the field. Although most of the column names are self-explanatory, if you are using any particular fields that may not be understood by all easily, you can put a small comment about it to explain this to everyone. This comment will be visible as a tooltip on the structure tab of that table. Also, the field names will be underlined to indicate this field has some particular info(a little different but similar to the primary key column that has been highlighted). The advantages of this are:

• Next time, anyone who sees that table structure can get a clear idea about the special column that he might not have been able to understand before and might have to ask you(wasting time for both of you).
• If you are working long-term in a system, then there is also a chance to forget about some unusual columns and what they are for exactly. This comment will also help you in those cases.
• If you or somebody else is going to clean up the database, this will help show unused columns that can be removed.

If you are not using the PhpMyadmin interface and want to set the DB engine by SQL query, you can use the following code sample for executing a query that will do the same as PHPMyAdmin:

/*your database user must have to have permission to execute this query, either for sql query window or executing script like php*/
ALTER TABLE `your_table_name` ENGINE = Innodb
Code language: JavaScript (javascript)

Other MySQL Best Practices to follow:

• Table naming. Try to use a prefix at the beginning of each table. This is very helpful when you want to set up two or more separate application/module tables in the same database; this will easily distinguish between them instead of mixing them up.
• Column naming. Try to use the same standard and convention in naming all table’s column names. This can be camel case, Pascal case, or lower case words separated by an underscore(this is most preferable to avoid unexpected case sensitivity problems). This is also essential/recommended by many database layer abstraction libraries (such as Doctrine), which auto-generate properties to/from the database and take care of the singular/plural form themselves.
• Try to use the proper data type and their length. If you assign an email field to length 255, it will be meaningless and a waste of memory.

As soon as you follow these database best practices for a few days, you will adjust to them, and as a result, you will go ahead with one more step in the field of good programming and development. I will try my best to share many more tips on best practices in the future. Happy programming 🙂