MySQL Best Practices

Database operations often tend to be the main bottleneck for most web applications today. As programmers need to do our part by structuring tables properly, writing optimized queries and better code.

Table Names

By convention, the “snake case” in lowercase (no PascalCase – we’re not using Microsoft SQL Server here!), plural name of the class will be used as the table name unless another name is explicitly specified. Tables should also be named based on their respective contents. Try to avoid naming tables based on a single use case and think more about how that table will be expanded in the future. If you’re not sure how to name the table ask your manager before creating it.

• eg: To find an order product we would look in the order_products table 

Primary Keys

The primary key is an incrementing integer value, which means that by default the primary key will be cast to an auto_increment int. This column should always be called id (lowecase)

Column Names

When naming columns try to name them with a more descriptive column name. This a great time to make things clearer for coding. If you are adding new columns to an existing field – make sure to not only keep the column types correct, but to name the column in a way that is clear to it’s contents.

Foreign Keys

For foreign keys, make sure to name the column with the linked table (order_id, product_id, product_version_id) lowercase, singular with the id. This makes joining and Active Records much easier to implement.  

Timestamps 

By default, each table should contain created_at and updated_at columns. The created_at should be a timestamp and the other a datetime field.

Table Types and Characters

By default all tables should be set to InnoDB with character set utf8_unicode_ci

CREATE TABLE `order_products` (
`id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
`created_at` timestamp NOT NULL,
`updated_at` datetime NOT NULL
) ENGINE='InnoDB' COLLATE 'utf8_unicode_ci';

Index the Search Fields

Indexes are not just for the primary keys or the unique keys. If there are any columns in your table that you will search by, you should almost always index them.

Index and Use Same Column Types for Joins

If your application contains many JOIN queries, you need to make sure that the columns you join by are indexed on both tables. This affects how MySQL internally optimizes the join operation.

Flags

In the event a flag needs to be set for turning things on or off, use a tinyint 1 for this column with a name matching the setting and be prefaced with set_ (set_active, set_importance, set_visible, etc). It will allow for the binary 0/1 as well as additional options in the event we need to add more options.

Related Tables

Tables that are related should be names accordingly. 

• orders
• order_products (foreign key to orders AND foreign key to product_versions)
• order_payments (foreign key to orders)
• products
• product_versions (foreign key to products)

Text or Blob?

Unless it is absolutely necessary to use a Text or Blob field they should be avoided. They slow down the whole table and MySQL changes the way it handles that table. If you must use it, then make sure to pick the correct type.

• TEXT is a data-type for text based input (like HTML or Copy).
• BLOB and CLOB which are more suitable for data storage (images, etc) due to their larger capacity limits (4GB for example)

New Table or New Column?

If the attribute/column depends on the entire primary key and nothing else, it belongs in the table.

If it depends on something other than, or in addition to, the primary key, it belongs elsewhere and the tables it belongs in should have a relationship with the current table.

• eg: Your name depends on your SSN so, if SSN was the primary key, your name would belong in that table. Your car or phone number does not depend entirely on your SSN (since you may have more than one car or phone, so it would go in a different table (your primary phone number may go in the first table).

Sources and Reference

What more information here’s some additional sources:

https://laravel.com/docs/master/eloquent

http://book.cakephp.org/3.0/en/orm/table-objects.html

https://framework.zend.com/manual/1.12/en/coding-standard.naming-conventions.html