• Skip to main content
  • Skip to primary sidebar
  • Skip to footer
  • Home
  • Featured
    • C# Tutorials
      • LinQ Tutorials
      • Facebook C# API Tutorials
    • PHP Tutorials
      • CodeIgniter Tutorials
    • Amazon AWS Tutorials
  • Categories
    • Programming
    • Development
    • Database
    • Web Server
    • Source Control
    • Management
    • Project
  • About
  • Write
  • Contact

CodeSamplez.com

Programming, Web development, Cloud Technologies

You are here: Home / Database / Basic MySQL Best Practices

Basic MySQL Best Practices

July 30, 2010 by Rana Ahsan 3 Comments

Programming & Development Tutorials

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

phpmyadmin database engineAlthough 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:

mysql column tool tip
comments show up as tool tip
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 🙂

Share If Liked

  • Click to share on Facebook (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
  • Click to share on Reddit (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • Click to share on Pocket (Opens in new window)

You may also like

Filed Under: Database, Development Tagged With: mysql, phpmyadmin

About Rana Ahsan

Rana is a passionate software engineer/Technology Enthusiast.
Github: ranacseruet

Reader Interactions

Comments

  1. Google SEO says

    August 20, 2010 at 10:01 pm

    Thanks a million for this, I appreciate the info

    Reply

Trackbacks

  1. Using foreign key constraint on mysql database | codesamplez.com says:
    October 24, 2010 at 2:47 am

    […] the opportunity that mysql/phpmyadmin gives in your development task. Also, don't forget to use best practices of database management all the times you are working. […]

    Reply
  2. Mysql Stored Procedures Programming Tutorial | codesamplez.com says:
    December 20, 2012 at 4:50 am

    […] can go through them also for more detailed and deep knowledge. Besides, don't forget to follow the database best practices as well. You can also ask if you have any, here by commenting. happy coding […]

    Reply

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 3,774 other subscribers

Follow Us

  • Twitter
  • Facebook

Top Posts & Pages

  • How To Work With JSON In Node.js / JavaScript
    How To Work With JSON In Node.js / JavaScript
  • PHP HTML5 Video Streaming Tutorial
    PHP HTML5 Video Streaming Tutorial
  • How To Work With C# Serial Port Communication
    How To Work With C# Serial Port Communication
  • Facebook C# API Tutorials
    Facebook C# API Tutorials
  • LinQ Query With Like Operator
    LinQ Query With Like Operator
  • Using Supervisord Web Interface And Plugin
    Using Supervisord Web Interface And Plugin
  • Get Facebook C# Api Access Token
    Get Facebook C# Api Access Token
  • Generate HTTP Requests using c#
    Generate HTTP Requests using c#
  • Getting Started With UDP Programming in Java
    Getting Started With UDP Programming in Java
  • Utilizing Config File In C#.NET Application
    Utilizing Config File In C#.NET Application

Recent Posts

  • Building Auth With JWT – Part 2
  • Building Auth With JWT – Part 1
  • Document Your REST API Like A Pro
  • Understanding Golang Error Handling
  • Web Application Case Studies You Must Read

Tags

.net angularjs apache api audio auth authenticatin aws c# cloud server codeigniter deployment docker doctrine facebook git github golang htaccess html5 http javascript jwt linq mysql nodejs oop performance php phpmyadmin plugin process python regular expression scalability server smarty socket.io tfs tips unit-test utility web application wordpress wpf

Footer

Archives

Follow Us

  • Twitter
  • Facebook

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 3,774 other subscribers

Copyright © 2023