MySQL naming convention

Currently I am working on a private project with some other people. We are trying to develop a new web-based system which will be using a MySQL database and a PHP interface. Because there are a couple of people working on the project the person responsible for creating the database model used Microsoft Access to create the basic database model. I installed a windows development server running MySQL in combination with PHP and a Apache webserver, I intended to run it on a Debian server but found out that the best support for the migration of Access to MySQL was on the Windows platform by using the MySQL migration toolkit.

After migrating from Access to MySQL I have been developing a lot of queries and functions, now I migrated from MySQL on windows to MySQL on a Linux server at the datacenter and suddenly found almost every query failing. The reason behind this is that because of the way MySQL is developed the object names on a windows platform are by default NOT case secetive and on a Linux/UNIX platform they are case sensitive.

In MySQL, databases and tables correspond to directories and files within those directories. Consequently, the case-sensitivity of the underlying operating system determines the case-sensitivity of database and table names. This means database and table names are case-insensitive in Windows, and case-sensitive in most varieties of Unix. One prominent exception here is Mac OS X, when the default HFS+ file system is being used.

Meaning, if you are working on a MySQL project remember that you will be needing a strong naming convention for all the database objects you will be creating during the project. Maybe you are convinced that it will always run on a windows server but it might change during the course of the project and you will have a hell of a time renaming all the objects in the database and in all the functions, queries and scripts.

In basic you can create your own naming convention for your database objects, use lowercase, use uppercase use them in combination as long as you know what th convention is and all the members of the team are aware of it and keep to the naming convention. My personal flavor is to use only uppercase in the naming of database objects. Use lowercase for the column names.

Some other rules that can make your like a lot easier is to start always with a prefix.

• TBL_{name of the table}
• V_{name of the view}
• SEQ_{name of the sequence}
• etc etc etc