Basic MySQL Best Practices

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

Why follow Database best practice conventions?

Suppose, if you can design a database and its table that is understandable easily to other developers who are working on the same database(or will work in future). In such cases, you will may 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 using 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 ‘MyIasm’ and ‘latin1_swedish_ci’ coresspondingly, but sometime, for a robust application they should be changed to suitable type for optimized support from database. Such as, if your application needs transaction operations and/or foreign key constraints strictly, then you must should use ‘Innodb’ as your database engine. It’s not required for all tables, but only for the tables that make use such operations. Similarly, ‘utf8_general_ci’ ad ‘utf8-unicode_ci’ are two general mostly used alternative form for collation/character-set. So, check and make necessary changes from your PhpMyadmin’s ‘operations’ tab of a particular table. You will get advantages like:

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

Database Table Column/Field Definitions:

Another point, when we are creating a table and filling the fields info, we always be in a hurry to only fill the necessary part like ‘auto increment’, ‘column name’, ‘type’,’length’ and just save the table. most of us don’t even see around to the other info/fields at all. But, they are 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 put some comment about the field. Although, most of the column names are self-explanatory, but if you are using any special 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 tool tip on the structure tab of that table. Also, the field names will be underlined to indicate this field have some special info(a little different but similar to primary key column that have underline). Advantages of this are:

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

If you are not using PhpMyadmin interface and want to set db engine by SQL query, you can use the following code sample for executing as 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

Other MySQL Best Practices to follow:

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

As soon as you follow these database best practices for few days, you will get adjusted with it and as a result, you will go ahead one more step in the filed of good programming and development. I will try my best to share much more best practice tips in future. Happy MySQL programming 🙂